Forum Discussion

PowerBI123456's avatar
PowerBI123456
Post Partisan
5 years ago

Headers Power Query

Hi,

 

I am combining several files from a folder. The first column (the source column), I need the column header to stay as "source name", but for the other columns, I need to promote the first row as header. How can I do promote heads for all but one column? 

 

Here is the M code:

let
Source = Folder.Files("XX),
#"Filtered Rows" = Table.SelectRows(Source, each ([Date modified] = #datetime(2020, 8, 28, 5, 1, 38.7773285) or [Date modified] = #datetime(2020, 8, 28, 5, 1, 50.7148695) or [Date modified] = #datetime(2020, 8, 28, 5, 2, 40.9643633))),
#"Filtered Hidden Files1" = Table.SelectRows(#"Filtered Rows", each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File (9)", each #"Transform File (9)"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File (9)"}),
#"Expanded Transform File (9)" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File (9)", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17", "Column18", "Column19", "Column20", "Column21", "Column22", "Column23", "Column24", "Column25"}, {"Transform File (9).Column1", "Transform File (9).Column2", "Transform File (9).Column3", "Transform File (9).Column4", "Transform File (9).Column5", "Transform File (9).Column6", "Transform File (9).Column7", "Transform File (9).Column8", "Transform File (9).Column9", "Transform File (9).Column10", "Transform File (9).Column11", "Transform File (9).Column12", "Transform File (9).Column13", "Transform File (9).Column14", "Transform File (9).Column15", "Transform File (9).Column16", "Transform File (9).Column17", "Transform File (9).Column18", "Transform File (9).Column19", "Transform File (9).Column20", "Transform File (9).Column21", "Transform File (9).Column22", "Transform File (9).Column23", "Transform File (9).Column24", "Transform File (9).Column25"})
in
#"Expanded Transform File (9)"

 

 

Thanks in advance!

Need Help
Tips and Tricks

5 Replies

  • Fowmy's avatar
    Fowmy
    Super User
    5 years ago

    PowerBI123456 

    After promoting the header, go and add the following code, it will rename only the first column:

    Table.RenameColumns(#"Promoted Headers",{{Table.ColumnNames(#"Promoted Headers"){0}, "Source.Name"}})



    You can paste this code in a blank Query and Check the steps:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCs7PTVVwSSxJVNJRcstMzUlRyEvMTQVyPP0igKRHcIBSrE60kiGQbQTExkBsAhYxBbLMgNgciC2UYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, #"TrasnformFile Column 1" = _t, #"TrasnformFile Column 2" = _t, #"TrasnformFile Column 3" = _t]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    Custom1 = Table.RenameColumns(#"Promoted Headers",{{Table.ColumnNames(#"Promoted Headers"){0}, "Source.Name"}})
in
    Custom1

    After



    Learned from ImkeF 

    ________________________

    If my answer was helpful, please consider Accept it as the solution to help the other members find it

    Click on the Thumbs-Up icon if you like this reply 🙂

    YouTube  LinkedIn

     

     

     

    • PowerBI123456's avatar
      PowerBI123456
      Post Partisan
      5 years ago

      @Fowmy Thanks! I am actually trying to do the inverse. I am trying to promote the first row as a header for all columns except the first column. Hope that makes sense.

      • AllisonKennedy's avatar
        AllisonKennedy
        Community Champion
        5 years ago
        PowerBI123456 you can't promote only some headers - that would cause mismatch in your rows of data, so you essentially have to promote ALL headers and then rename the first column. Hope that makes sense?
  • AllisonKennedy's avatar
    AllisonKennedy
    Community Champion
    5 years ago
    PowerBI123456
    Another thing you can try is to promote your headers in the custom function first, then you won't have this problem. Look for
    Transform File (9)
    in your queries pane on left hand side, then click into the sample file query steps and add the promoted headers step to the sample file. This should update the custom function and eliminate the need to promote headers in the final query (and also get rid of header rows in the middle of your data that might have been messing things up before).