M code to promote headers within Table.ExpandTableColumn function

M

masterelaichi

New member
Joined
Feb 27, 2019
Messages
25
Reaction score
0
Points
1
Excel Version(s)
2016
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
 

Attachments

  • Promoted headers.JPG
    Promoted headers.JPG
    25 KB · Views: 49
Sure:

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

Cheers,[/COLOR]
 
Ken Puls said:
Sure:

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


Cheers,
Click to expand...


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
 

Attachments

  • Expandtable1.JPG
    Expandtable1.JPG
    20.3 KB · Views: 26
  • Expandtable2.JPG
    Expandtable2.JPG
    16.2 KB · Views: 15
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.
 
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

Code: 
 #"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"
 
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.
 
masterelaichi said:
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
Click to expand...



Is transforming the field first sufficient and then expand it?

Table.TransformColumns(#"Expanded ExtractInitiative", {{"Data", Table.PromoteHeaders, type table}})
 
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?
 
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

Code: 
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 ?
 
cyborgski said:
Is transforming the field first sufficient and then expand it?

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


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
 
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:

Code: 
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:
Code: 
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.
 
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
 
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
 
You must log in or register to reply here.
Back
Top