Promote the first line in header but by filtering the source.name

[Anonymous]

Hi everyone,

How to promote the first line in header while filtering the source.name column which it should not change. I would point out that renaming is not a good idea because the folder option behind bug if we change the source file.

Thank you very much 😁

Charly

[Anonymous]

Hi Charly - 

I am sorry to say but it was unclear of your ask, what you are trying to achieve here. May I request you to share a screenshot or something as an example and show what is required to be done?

Regards

E

[Anonymous]

Thank you for your answers @Anonymous  @MFelix . Above I want to promote in header what I highlight in yellow but not the first column.

The source name column must not change its name. Manual renaming thereafter causes bugs when the source changes.

[MFelix · Accepted solution]

Hi @Anonymous ,

Do the following steps:

• Add an index column
• Add a custom column with the following code:
  • if [Index] = 0 then "Source.Name" else [Source.Name]
    • If your index starts at 1 replace 0 by 1
• Remove the Source.Name and Index column
• Promote headers

This will create a new column where the first cell will be Source.Name the rest will be the file name

Check code below for an example:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcsvMSc1LzE1V0lHySE1MSS1SMEQwjRBMYygzsUjBRClWB0VjcUoakEwrToGTIJHYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Source.Name = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Source.Name", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each if [Index] = 0 then "Source.Name" else [Source.Name]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Source.Name", "Index"}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Columns", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Header 1", type text}, {"Header 2", type text}, {"Header 3", type text}, {"Headar 4", type text}, {"0", Int64.Type}, {"Source.Name", type text}})
in
    #"Changed Type1"

[Anonymous]

Top !! Thank you @MFelix 

[MFelix]

Hi @Anonymous ,

I'm assuming that you are refering to having an automation to select one file and get the headers promoted?

You need to create a custom function to make the headers promotion and then use it on a list of files then you can select the file you want and the headers will be promoted.

Be aware that if the number of columns are different this can cause an error also when going back to the datamodel you will have errors because the columns names may not be matching so the visualizations will not work.