Page 1 of 2 1 2 LastLast
Results 1 to 10 of 13

Thread: M code to promote headers within Table.ExpandTableColumn function

  1. #1
    Seeker masterelaichi's Avatar
    Join Date
    Feb 2019
    Posts
    6
    Articles
    0
    Excel Version
    2016

    M code to promote headers within Table.ExpandTableColumn function



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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
    Attached Thumbnails Attached Thumbnails Click image for larger version. 

Name:	Promoted headers.JPG 
Views:	8 
Size:	25.0 KB 
ID:	9331  

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,341
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Sure:

    Code:
    Table.AddColumn(Table.ExpandTableColumn(Custom1,"ExtractInitiative",{"Data"}), "Custom", each Table.PromoteHeaders([Data]))


    Cheers,
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    Seeker masterelaichi's Avatar
    Join Date
    Feb 2019
    Posts
    6
    Articles
    0
    Excel Version
    2016
    Quote Originally Posted by Ken Puls View Post
    Sure:

    Code:
    Table.AddColumn(Table.ExpandTableColumn(Custom1,"ExtractInitiative",{"Data"}), "Custom", each Table.PromoteHeaders([Data]))


    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
    Attached Thumbnails Attached Thumbnails Click image for larger version. 

Name:	Expandtable1.JPG 
Views:	10 
Size:	20.3 KB 
ID:	9332   Click image for larger version. 

Name:	Expandtable2.JPG 
Views:	8 
Size:	16.2 KB 
ID:	9333  

  4. #4
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,341
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    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.
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  5. #5
    Seeker masterelaichi's Avatar
    Join Date
    Feb 2019
    Posts
    6
    Articles
    0
    Excel Version
    2016
    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"

  6. #6
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,652
    Articles
    0
    Excel Version
    O365
    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.

  7. #7
    Acolyte cyborgski's Avatar
    Join Date
    May 2019
    Posts
    43
    Articles
    0
    Excel Version
    Office 365
    Quote Originally Posted by masterelaichi View Post
    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}})

  8. #8
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,341
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    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?
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  9. #9
    Seeker masterelaichi's Avatar
    Join Date
    Feb 2019
    Posts
    6
    Articles
    0
    Excel Version
    2016
    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 ?

  10. #10
    Seeker masterelaichi's Avatar
    Join Date
    Feb 2019
    Posts
    6
    Articles
    0
    Excel Version
    2016
    Quote Originally Posted by cyborgski View Post
    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

Page 1 of 2 1 2 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •