M code to promote headers within Table.ExpandTableColumn function

[masterelaichi]

Hi,

I am trying alter the M code to promote headers within an ExpandTableColumn function. I am trying to do this without having to add an extra column

Step 1 => Table.ExpandTableColumn(Custom1,"ExtractInitiative",{"Data"})

Data is a column with nested tables. The goal is to promote headers in these inner tables

The method I have employed is to add an extra column

Step 2 => Table.AddColumn(#"Expanded ExtractInitiative", "Custom", each Table.PromoteHeaders([Data]))

Is there a way to combine it so that I can do this within Step 1? Hopefully the screenshot helps


Thanks

 

[Ken Puls]

Sure:

[COLOR=#333333]Table.AddColumn([/COLOR][B][COLOR=#333333]Table.ExpandTableColumn(Custom1,"ExtractInitiative",{"Data"})[/COLOR][/B][COLOR=#333333], "Custom", each Table.PromoteHeaders([Data]))

Cheers,[/COLOR]

 

[masterelaichi]

Sure:

[COLOR=#333333]Table.AddColumn([/COLOR][B][COLOR=#333333]Table.ExpandTableColumn(Custom1,"ExtractInitiative",{"Data"})[/COLOR][/B][COLOR=#333333], "Custom", each Table.PromoteHeaders([Data]))[/COLOR]

Cheers,

Hi Ken

Thanks for that but that's not exactly what I was wanting to do. I wanted to avoid adding that extra column

Can this code Table.ExpandTableColumn(Custom1,"ExtractInitiative", {"Data"}) be manipulated such that when I click on the table preview, the table inside has promoted header already ? So that once I expand, it gets expanded with the 1st columns as headers already


I am thinking that it's got something to do with tweaking the {"Data"} of the code above

 

[Ken Puls]

Sorry, hard to follow when I'm only getting snippets of the code. I'm not following your step order, as it seems backwards to me.

Can you go to View -> Advanced Editor and copy the entire query and past it here? It would be much easier to debug if I can see the entire block of code.

 

[masterelaichi]

Hi Ken

Here is small part of the code. As the source file is an office sharepoint site, I am not able to send that to you. Hope this helps. Otherwise, Ill dummy up an excel file to simulate my problem. If that is better, I'll do that

 #"Removed Other Columns1" = Table.SelectColumns(#"Added Custom1",{"SelectInfoCollecColumn"}),
    #"Expanded SelectInfoCollecColumn" = Table.ExpandTableColumn(#"Removed Other Columns1", "SelectInfoCollecColumn", {"Content", "Name"}, {"Content", "Name"}),
    #"Expanded Content1" = Table.ExpandTableColumn(#"Expanded SelectInfoCollecColumn", "Content", {"Content", "Name"}, {"Content.Content", "Content.Name"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Content1",each Text.Contains([Content.Name], "MASTER") and Text.StartsWith([Content.Name], "2018") or Text.Contains([Content.Name], "MASTER") and Text.StartsWith([Content.Name], "2019")),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Filtered Rows", "Content.Name", Splitter.SplitTextByEachDelimiter({"-"}, QuoteStyle.Csv, false), {"Content.Name.1", "Content.Name.2"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Content.Name.1", type text}, {"Content.Name.2", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Content.Name.1", "Report As At"}}),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Renamed Columns", {{"Report As At", type date}}, "en-AU"),
    #"Filtered Rows1" = Table.SelectRows(#"Changed Type with Locale", let latest = List.Max(#"Changed Type with Locale"[Report As At]) in each [Report As At] = latest),
    #"Sorted Rows" = Table.Sort(#"Filtered Rows1",{{"Report As At", Order.Descending}}),
    // M code extratc excel content from Binary files
    // Select rows which contain "." 
    // Keeps Name and Data columns only
    ExtractInitiativefromTable = 

/*2 ------> */ Table.AddColumn(#"Sorted Rows", "ExtractInitiative", each Table.SelectColumns( 
  Table.SelectRows( Excel.Workbook([Content.Content]), each Text.Contains ([Name],".")
         ),
{"Name","Data"}
)),
    KeepReportandExtractInitiative = Table.SelectColumns(ExtractInitiativefromTable,{"Report As At", "ExtractInitiative"}),
    #"Expanded ExtractInitiative" = Table.ExpandTableColumn(KeepReportandExtractInitiative, "ExtractInitiative", {"Data"}, {"Data"})
in
    #"Expanded ExtractInitiative"

 

[Bob Phillips]

What is the problem with adding a column? One of the beauties of Power Query is that you can always remove unwanted columns in the next step. Add a column to expand the data, and delete the original. No problem.

 

[cyborgski]

Hi,

I am trying alter the M code to promote headers within an ExpandTableColumn function. I am trying to do this without having to add an extra column

Step 1 => Table.ExpandTableColumn(Custom1,"ExtractInitiative",{"Data"})

Data is a column with nested tables. The goal is to promote headers in these inner tables

The method I have employed is to add an extra column

Step 2 => Table.AddColumn(#"Expanded ExtractInitiative", "Custom", each Table.PromoteHeaders([Data]))

Is there a way to combine it so that I can do this within Step 1? Hopefully the screenshot helps


Thanks

Is transforming the field first sufficient and then expand it?

Table.TransformColumns(#"Expanded ExtractInitiative", {{"Data", Table.PromoteHeaders, type table}})

 

[Ken Puls]

Honestly, I've got to say I'm with Bob on this one. Why are you trying to consolidate the steps? It really won't impact performance, obfuscates the code, and makes it harder for someone (else) to maintain.

I mean, it can be done, but what's the real reason you want to?

 

[masterelaichi]

Hi Ken,

There is no business reason for me to do this (atleast one I haven't come across so far). It is purely from curiosity as I was just messing around trying to understand how expand table works. The idea stuck me when I was watching a video by Mike Girvin of ExcelIsFun where he does some aggregation within Table.GroupBy functions

My original problem involves multiple excel workbooks within a sharepoint link. So when I click on the expand table columns, I get Data column with nested tables in it

Now I have tried to simulate this problem as much as possible by creating a dummy file. Here is the code for that

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Tcy7CoAwDAXQf8kseFsddFRcRZ2LQ9EigqiUOvj3ptFBAjcPDjGGOj8739jgKKHeH/M1BZ6GcMu+To7GxJBGiiLVUCWfq/PcIleQwCvUKxBFbXcuHjQkPqJ/Tzpv9yV+ySDxkexHWhYH9xwSLMYH", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]),
    #"Added Custom" = Table.AddColumn(Source, "ReportDate", each #date(2019,08,24) ),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Custom",{"ReportDate", "Column1", "Column2", "Column3", "Column4"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Reordered Columns",{{"ReportDate", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ReportDate"}, {{"AllRows", each _, type table [ReportDate=date, Column1=text, Column2=text, Column3=text, Column4=text]}}),
    #"Added Custom1" = Table.AddColumn(#"Grouped Rows", "PromoteHeaders", each Table.PromoteHeaders([AllRows])),
    #"Expanded PromoteHeaders" = Table.ExpandTableColumn(#"Added Custom1", "PromoteHeaders", {"OrderDate", "Product", "Qty", "Price"}, {"OrderDate", "Product", "Qty", "Price"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded PromoteHeaders",{"AllRows"})
in
    #"Removed Columns"

Is there a way to promote headers in the group by itself; possibly extending the same concept to the Table.ExpandTableColumn function ?

 

[masterelaichi]

Is transforming the field first sufficient and then expand it?

Table.TransformColumns(#"Expanded ExtractInitiative", {{"Data", Table.PromoteHeaders, type table}})

this one sort of worked. But because the "Data" column contains tables, I was trying to promote headers inside the Expandtable step, possibly by manipulating the {"Data} of the code

Table.ExpandTableColumn(Custom1,"ExtractInitiative",{"Data"})

So that when I click on the white space of a cell to preview the data, the tables already have headers promoted

 

[Ken Puls]

All I did here was copy the preceding step's formula in place of the step name, then deleted the prior step, but it works:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Tcy7CoAwDAXQf8kseFsddFRcRZ2LQ9EigqiUOvj3ptFBAjcPDjGGOj8739jgKKHeH/M1BZ6GcMu+To7GxJBGiiLVUCWfq/PcIleQwCvUKxBFbXcuHjQkPqJ/Tzpv9yV+ySDxkexHWhYH9xwSLMYH", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]),
    #"Added Custom" = Table.AddColumn(Source, "ReportDate", each #date(2019,08,24) ),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Custom",{"ReportDate", "Column1", "Column2", "Column3", "Column4"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Reordered Columns",{{"ReportDate", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ReportDate"}, {{"AllRows", each _, type table [ReportDate=date, Column1=text, Column2=text, Column3=text, Column4=text]}}),
    #"Expanded PromoteHeaders" = Table.ExpandTableColumn(Table.AddColumn(#"Grouped Rows", "PromoteHeaders", each Table.PromoteHeaders([AllRows])), "PromoteHeaders", {"OrderDate", "Product", "Qty", "Price"}, {"OrderDate", "Product", "Qty", "Price"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded PromoteHeaders",{"AllRows"})
in
    #"Removed Columns"

You could then do the same with the removed columns, if you wanted:

let    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Tcy7CoAwDAXQf8kseFsddFRcRZ2LQ9EigqiUOvj3ptFBAjcPDjGGOj8739jgKKHeH/M1BZ6GcMu+To7GxJBGiiLVUCWfq/PcIleQwCvUKxBFbXcuHjQkPqJ/Tzpv9yV+ySDxkexHWhYH9xwSLMYH", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]),
    #"Added Custom" = Table.AddColumn(Source, "ReportDate", each #date(2019,08,24) ),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Custom",{"ReportDate", "Column1", "Column2", "Column3", "Column4"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Reordered Columns",{{"ReportDate", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ReportDate"}, {{"AllRows", each _, type table [ReportDate=date, Column1=text, Column2=text, Column3=text, Column4=text]}}),
    #"Removed Columns" = Table.RemoveColumns(Table.ExpandTableColumn(Table.AddColumn(#"Grouped Rows", "PromoteHeaders", each Table.PromoteHeaders([AllRows])), "PromoteHeaders", {"OrderDate", "Product", "Qty", "Price"}, {"OrderDate", "Product", "Qty", "Price"}),{"AllRows"})
in
    #"Removed Columns"

But here's the thing... it will still take exactly the same time, as the same steps are being followed, but obfuscates the code.

 

[masterelaichi]

Thanks Ken

Appreciate your help. My workbook has multiple merged queries which takes a long time run. I was thinking that reducing the steps might speed it up

 

[Ken Puls]

Reducing the steps might, but it’s not the first place I would look. (Honestly, it’s usually not the way to solve the issue.)

Sometimes adding index columns to the tables before merging can speed things up, sometimes buffering tables can help. But perf issues are a tough thing to diagnose in power query today.


Sent from my iPhone using Tapatalk