Headers Power Query

[PowerBI123456]

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!

[AllisonKennedy]

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

[Fowmy]

@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]

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

[Fowmy]

@PowerBI123456

In that case, what do you want to do with the first value of the first column?

You can rename all the columns with the values of the first row except, then I Jay would be the next step?

________________________

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

[AllisonKennedy]

@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?