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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Tom_Y
Helper III
Helper III

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

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!

1 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. 

vjingzhanmsft_0-1710728280215.png

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

View solution in original post

2 REPLIES 2
Tom_Y
Helper III
Helper III

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"?

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. 

vjingzhanmsft_0-1710728280215.png

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

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors