Page 2 of 2 FirstFirst 1 2
Results 11 to 13 of 13

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

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


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

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

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

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

Page 2 of 2 FirstFirst 1 2

Posting Permissions

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