Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

New column from Onedrive / sharepoint not showing after refresh.

Hello everyone.

My problem is really simple but ufortunally I havent come with a solution.

 

I had an excel on a sharepoint folder and I added a new column to the file (a new category came up)., but when I refresh my power query the new column does not appear. I dont have the colums=x.

 

please, I need some help.

 

thanks

8 REPLIES 8
Anonymous
Not applicable

I have just come across this so you've probably solved but it may help others.   If you've connected to a file in SharePoint and got to the point where you have an identified file to expand to review content; it is this point in your code you should go back to.  Expand the 'Content' 'Binary' and it will reconnect to the file and hey presto it will pick up the additional column(s)..  

Hi @Anonymous, 

 

I have come across the exact same issue. I have read many threads with no fix. 

What do you mean by expand the content? 

Anonymous
Not applicable

CoderWhenNeeded_0-1661844135385.png

Apologies this was not clear.  I meant in the table view of the source data that you expand the table again i.e. click on the arrows icon e.g. on the yellow highlighted column in the image above.

Thank you. I have solved this issue. 

v-stephen-msft
Community Support
Community Support

Hi @Anonymous ,

 

There is a 1 GB limit per dataset that is imported into Power BI. It may be that the size of the data set exceeds the limit and caused an error. Please try to reduce the size of the data set.

 

Best Regards,

Stephen Tao

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

edhans
Super User
Super User

Can you post the M code? The Excel.Workbook() function doesn't have the columns=x parameter. That is for CSV files. 

If you connected to a named range and didn't extend the range to include your new column that could be the issue. It could also be the issue that you connected to a Table in Excel, but for some reason the table didn't auto-expand to include the new column. It is rare, but i've seen it happen.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Anonymous
Not applicable

Thanks my friend.  Here is the M code

let
Source = SharePoint.Files("https://carcgl-my.sharepoint.com/personal/damianarturo_serna_carrier_com", [ApiVersion = 15]),
#"Filtered Rows" = Table.SelectRows(Source, each Text.Contains([Name], "Wesco data")),
#"Filtered Hidden Files1" = Table.SelectRows(#"Filtered Rows", each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File (2)", each #"Transform File (2)"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File (2)"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File (2)", Table.ColumnNames(#"Transform File (2)"(#"Sample File (2)"))),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Transfer Cust", type any}, {"Item Number", type text}, {"Description", type text}, {"Date", Int64.Type}, {"Transaction Time", Int64.Type}, {"WIS Order Number", Int64.Type}, {"Customer Purchase Order", type text}, {"Print Line Number", Int64.Type}, {"Invoice Number", Int64.Type}, {"Transaction Code", type text}, {"D=Debit C=Credit", type text}, {"Quantity", Int64.Type}, {"Currency", type text}, {"Price", type number}, {"Extension", type number}, {"User ID", type text}, {"User full name", type text}, {"Approver full name", type text}, {"Centro de costos", type text}, {"Sub-cuenta", Int64.Type}, {"Numero de maquina y descripc", type text}, {"Code", type text}, {"Area", type text}, {"Equipment Family", type text}, {"Currency Type", type any}, {"Bin Location", type text}, {"Location Name", type any}, {"Customer Number", Int64.Type}, {"Comments", type any}, {"Requestor Name", type text}, {"Commodity code", Int64.Type}, {"Commodity description", type text}, {"critical item flag", type text}}),
#"Inserted Last Characters" = Table.AddColumn(#"Changed Type", "Last Characters", each Text.End(Text.From([Date], "en-US"), 2), type text),
#"Added Prefix" = Table.TransformColumns(#"Inserted Last Characters", {{"Last Characters", each "20" & _, type text}}),
#"Inserted Last Characters1" = Table.AddColumn(#"Added Prefix", "Last Characters.1", each Text.End(Text.From([Date], "en-US"), 4), type text),
#"Extracted First Characters" = Table.TransformColumns(#"Inserted Last Characters1", {{"Last Characters.1", each Text.Start(_, 2), type text}}),
#"Split Column by Position" = Table.SplitColumn(Table.TransformColumnTypes(#"Extracted First Characters", {{"Date", type text}}, "en-US"), "Date", Splitter.SplitTextByPositions({0, 4}, true), {"Date.1", "Date.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Position",{{"Date.1", Int64.Type}, {"Date.2", Int64.Type}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Date.2"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Source.Name", "Transfer Cust", "Item Number", "Description", "Transaction Time", "WIS Order Number", "Customer Purchase Order", "Print Line Number", "Invoice Number", "Transaction Code", "D=Debit C=Credit", "Quantity", "Currency", "Price", "Extension", "User ID", "User full name", "Approver full name", "Centro de costos", "Sub-cuenta", "Numero de maquina y descripc", "Code", "Area", "Equipment Family", "Currency Type", "Bin Location", "Location Name", "Customer Number", "Comments", "Requestor Name", "Commodity code", "Commodity description", "critical item flag", "Last Characters", "Last Characters.1", "Date.1"}),
#"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Reordered Columns", {{"Date.1", type text}}, "en-US"),{"Last Characters", "Date.1", "Last Characters.1"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Date"),
#"Changed Type2" = Table.TransformColumnTypes(#"Merged Columns",{{"Date", type date}}),
#"Filtered Rows1" = Table.SelectRows(#"Changed Type2", each true),
#"Reordered Columns1" = Table.ReorderColumns(#"Filtered Rows1",{"Date", "Source.Name", "Transfer Cust", "Item Number", "Description", "Transaction Time", "WIS Order Number", "Customer Purchase Order", "Print Line Number", "Invoice Number", "Transaction Code", "D=Debit C=Credit", "Quantity", "Currency", "Price", "Extension", "User ID", "User full name", "Approver full name", "Centro de costos", "Sub-cuenta", "Numero de maquina y descripc", "Code", "Area", "Equipment Family", "Currency Type", "Bin Location", "Location Name", "Customer Number", "Comments", "Requestor Name", "Commodity code", "Commodity description", "critical item flag"}),
#"Removed Columns1" = Table.RemoveColumns(#"Reordered Columns1",{"Source.Name", "Transfer Cust", "Currency"}),
#"Inserted Merged Column" = Table.AddColumn(#"Removed Columns1", "Merged", each Text.Combine({Text.From([Date], "en-US"), Text.From([Transaction Time], "en-US")}, " "), type text),
#"Replaced Value" = Table.ReplaceValue(#"Inserted Merged Column","STRETCH NANOFLEX PEL�CULA DE PL‡STICO EMPLAYER 18 60 800 FT SMNA1500, (ROLL","STRETCH NANOFLEX PELICULA DE PLASTICO EMPLAYER 18 60 800 FT SMNA1500, (ROLL",Replacer.ReplaceValue,{"Description"})
in
#"Replaced Value"

Ahh... you are using the combine files feature. Go look at the Transform Sample File query. Is there any code in there that is limiting the columns?

Use the </> code button to paste code so it is a bit easier to read. It might be on the 2nd menu bar.

edhans_0-1620834450281.png

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors