Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Anonymous
Not applicable

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

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

1 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"

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

5 REPLIES 5
Anonymous
Not applicable

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
Not applicable

Capture.PNG

 

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.

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"

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

Top !! Thank you @MFelix 

MFelix
Super User
Super User

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.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.