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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. 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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors