cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mukeshkumar3010 Frequent Visitor
Frequent Visitor

Re: Expanding Multiple Data Tables with Unknown Column Names

Hi @ImkeF 

 

I have a folder with multiple .xls file with same structure, it will change in future. I am trying to append all as a single file and but I don't need all the columns. I have around 40 columns and as per business we need only 20 columns. So I reffered your method of expanding the column dynamically. Below code works but it is too much slow.

 

let
    Source = Folder.Files("E:\Project\TestFiles"),
    Step1 = Table.SelectColumns(Source,{"Content"}),
    Step2 = Table.AddColumn(Step1, "Custom", each Excel.Workbook([Content])),
    Step3 = Table.SelectColumns(Step2, {"Custom"}),
    Step4 = Table.ExpandTableColumn(Step3, "Custom", {"Name", "Data"}, {"Name", "Data"}),
    Step5 = Table.RemoveColumns(Step4 ,{"Name"}),
    Step6  = Table.AddColumn(Step5, "PromoteHeader", each Table.PromoteHeaders([Data])),
    Step7 = Table.SelectColumns(Step6,{"PromoteHeader"}),
    DistinctColumn = List.Union(List.Transform(Table.Column(Step7,Table.ColumnNames(Step7){0}), each Table.ColumnNames(_))),
    ExpandTable = Table.ExpandTableColumn(Step7,"PromoteHeader",DistinctColumn,DistinctColumn)
in
    ExpandTable

For testing i just had 1 .xls file of 1.5Mb in folder but it was too slow and in status bar it was showing loading more than 500Mb( clueless how ). So again I tried using single excel file with below code and it worked perfectly fine.

 

let
    Source = Excel.Workbook(File.Contents("E:\Project\TestFiles\Data.xls"), null, true),
    #"Added Custom" = Table.AddColumn(Source, "PromoteHeader", each Table.PromoteHeaders([Data])),
    #"Removed Columns" = Table.SelectColumns(#"Added Custom",{"PromoteHeader"}),
    DistinctColumn = List.Union(List.Transform(Table.Column(#"Removed Columns",Table.ColumnNames(#"Removed Columns"){0}), each Table.ColumnNames(_))), 
    ExpandTable =Table.ExpandTableColumn(#"Removed Columns","PromoteHeader",DistinctColumn,DistinctColumn)
in
    ExpandTable

Now my question is:- 1. Why it is too slow for the first code? I am mising anything.

                                    2. How can it be fixed.

 

Hope the above explanation gives you idea what I am trying to achive.

Super User
Super User

Re: Expanding Multiple Data Tables with Unknown Column Names

Hi @mukeshkumar3010 ,

I have some questions for you:

 

1) Why do you ignore the questions from my last post that would help me solve your problem?

 

2) Why do you pretend that your first codes worked if they couldn't? I have wasted my time with your faulty code and now you don't even bother to excuse for that. I've highlighted the corrections you've made below:

 

faulty code:
let
    Source = Excel.Workbook(File.Contents(""E:\Project\TestFiles\Data.xlsx"), null, true),
    #"Added Custom" = Table.AddColumn(Source, "PromoteHeader", each Table.PromoteHeaders([Data])),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Data"}),
    DistinctColumn = List.Union(List.Transform(Table.Column(#"Removed Columns",Table.ColumnNames(#"Removed Columns"){0}), each Table.ColumnNames(_))), 
    ExpandTable =Table.ExpandTableColumn(#"Removed Columns","PromoteHeader",DistinctColumn,DistinctColumn)
in
    ExpandTable 
	
correct code:	
let
    Source = Excel.Workbook(File.Contents("E:\Project\TestFiles\Data.xls"), null, true),
    #"Added Custom" = Table.AddColumn(Source, "PromoteHeader", each Table.PromoteHeaders([Data])),
    #"Removed Columns" = Table.SelectColumns(#"Added Custom",{"PromoteHeader"}),
    DistinctColumn = List.Union(List.Transform(Table.Column(#"Removed Columns",Table.ColumnNames(#"Removed Columns"){0}), each Table.ColumnNames(_))), 
    ExpandTable =Table.ExpandTableColumn(#"Removed Columns","PromoteHeader",DistinctColumn,DistinctColumn)
in
    ExpandTable	
faulty code:
let
    Source = Folder.Files("E:\Project\TestFiles"),
    Step1 = Table.SelectColumns(Source,{"Content"}),
    Step2 = Table.AddColumn(Step1, "Custom", each Excel.Workbook([Content])),
    Step3 = Table.SelectColumns(Step2, {"Custom"}),
    Step4 = Table.ExpandTableColumn(Step3, "Custom", {"Name", "Data"}, {"Name", "Data"}),
    Step5 = Table.RemoveColumns(Step5 ,{"Name"}),
    Step6  = Table.AddColumn(Step6, "PromoteHeader", each Table.PromoteHeaders([Data])),
    Step7 = Table.SelectColumns(Step6,{"PromoteHeader"}),
    DistinctColumn = List.Union(List.Transform(Table.Column(Step7,Table.ColumnNames(Step7){0}), each Table.ColumnNames(_))),
    Step8 =Table.ExpandTableColumn(Step7,"PromoteHeader",DistinctColumn,DistinctColumn)
in
    Step8


correct code:
let
    Source = Folder.Files("E:\Project\TestFiles"),
    Step1 = Table.SelectColumns(Source,{"Content"}),
    Step2 = Table.AddColumn(Step1, "Custom", each Excel.Workbook([Content])),
    Step3 = Table.SelectColumns(Step2, {"Custom"}),
    Step4 = Table.ExpandTableColumn(Step3, "Custom", {"Name", "Data"}, {"Name", "Data"}),
    Step5 = Table.RemoveColumns(Step4 ,{"Name"}),
    Step6  = Table.AddColumn(Step5, "PromoteHeader", each Table.PromoteHeaders([Data])),
    Step7 = Table.SelectColumns(Step6,{"PromoteHeader"}),
    DistinctColumn = List.Union(List.Transform(Table.Column(Step7,Table.ColumnNames(Step7){0}), each Table.ColumnNames(_))),
    ExpandTable = Table.ExpandTableColumn(Step7,"PromoteHeader",DistinctColumn,DistinctColumn)
in
    ExpandTable	

?

 

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




mukeshkumar3010 Frequent Visitor
Frequent Visitor

Re: Expanding Multiple Data Tables with Unknown Column Names

Hi @ImkeF 

 

I am really sorry for the mistake and didn't highlighted the change instead of placing the new code and resulting in loss of your valuable time. I posted first with different system so had to write the code manaully by seeing the code and I may overlooked at few points and after submit it showed an error and I had to rewrite entired post again and caused wrong code. This is my first attempt to post in the forum and i I really sorry for my error in code and your time as well. I will be very careful in posting the code again.

Super User
Super User

Re: Expanding Multiple Data Tables with Unknown Column Names

Thank you @mukeshkumar3010 , very much appreciated.

 

You can try to buffer the list with the column names like so:

 

 DistinctColumn = List.Buffer(List.Union(List.Transform(Table.Column(Step7,Table.ColumnNames(Step7){0}), each Table.ColumnNames(_))))

 

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




mukeshkumar3010 Frequent Visitor
Frequent Visitor

Re: Expanding Multiple Data Tables with Unknown Column Names

Hi @ImkeF 

 

It solved my issue than you for your guidance 🙂 , just want to understand what this code actually did? Did it forced to do calculation at Distinct column?

Highlighted
Super User
Super User

Re: Expanding Multiple Data Tables with Unknown Column Names

Hi @mukeshkumar3010 ,

that's good to hear 😉

Buffering shall prevent the same expression unneccessarliy being evluated multiple times. Sometimes it works and sometimes not, so it's always worth trying out.

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 307 members 3,252 guests
Please welcome our newest community members: