Fail to get all the data from 250+ columns after expand

[Tom_Y]

Hi. I do need some help. Thanks.

The code is not very complicated but I suddenly find that I did't get all the columns from the source files

There're 5 excel files for Year 2020 - 2024, all in same folder in sharepoint.

Actually they're in same format, same headers, over 250 columns.

let
Source = SharePoint.Contents("https://xxxx.sharepoint.com/sites/Projects", [ApiVersion = 15]),
#"Shared Documents" = Source{[Name="Shared Documents"]}[Content],
#"xxx Dashboard" = #"Shared Documents"{[Name="xxx Dashboard"]}[Content],
#"xxxx Advice" = #"xxx Dashboard"{[Name="xxxx Advice"]}[Content],
#"Filtered Rows" = Table.SelectRows(#"xxxx Advice", each Text.StartsWith([Name], "xxxx Advice all xxxx")),
#"Removed Other Columns1" = Table.SelectColumns(#"Filtered Rows",{"Content"}),
#"Filtered Hidden Files1" = Table.SelectRows(#"Removed Other Columns1", each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
PreExpand = Table.SelectColumns(#"Invoke Custom Function1",{"Transform File"}),
HEADINGS = List.Buffer(List.Union(List.Transform(PreExpand[Transform File], each Table.ColumnNames(_)))),
ReadyToExpand = PreExpand,
#"Expanded Transform File" = Table.ExpandTableColumn(ReadyToExpand, "Transform File", HEADINGS),

after this step, all the excels are combined, but then 3 out of that 5 files only load the first 246 columns, and all columns after that is all "null".

I've tried to restrict the filter so only 1 excel is remained, and the problem still exist. So the problem exists before expand.

When I preview the table in #"Invoke Custom Function1", the column is already missing.

Anyone can point me direction where to look into? The column headers seem normal.

Thank you so much!

[Tom_Y]

I got it after a bad sleep. It's in the help folder --> Transform file

I don't know why, but it was restrcited to Columns=246, and I just change it to null

= (Parameter1 as binary) => let
Source = Csv.Document(Parameter1,[Delimiter=",", Columns=null, Encoding=65001, QuoteStyle=QuoteStyle.Csv]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true])
in
#"Promoted Headers"

Any way we can skip the "Transform file"?

[v-jingzhan-msft · Accepted solution]

Hi @Tom_Y 

#"Transform File" is a custom function which is binded to the #"Transform Sample File" query. The code of #"Transform File" is updated automatically according to the changes in the #"Transform Sample File" query. In #"Transform Sample File" query, you can do some transformations based on the sample file. These transformations will then be applied to all files in the folder before combining. This is done by invoking the #"Transform File" function in the combined query. 

So usually you need to check the result of the #"Transform Sample File" query. Ensure its result is ready for the file before combining. Selecting an appropriate file as sample file is also important as the Source step code is generated by analyzing the sample file. If a sample file contains fewer columns than other files, it will probably only get those columns for all files. But you can modify the code of #"Transform Sample File" query to change the result. 

I recommend that you modify the code in #"Transform Sample File". This can let you check the result after each step visibly. Otherwise if you modify #"Transform File" directly, this will break the binding and make it not updated according to #"Transform Sample File" any more. 

Best Regards,
Jing