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, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
baptiste23
Frequent Visitor

Transforming Multiple Header file

Dear Power Bi Community

 

I am currently trying to connect Power BI to an excel file which has multiple columns header and I can't manage to transform the file properly to be able to get the best out of it.

 

The data set is available here: We transfer link and please find a preview below:Annotation 2020-06-15 092055.png

 

Do any of you know if it's possible to transform the file thanks to Power Query ?

 

My aim is to have the columns: Club, Country, Position, Country ..  as headers so I can make my analysis using those axes but I need to keep the rows above as they are also useful.

 

Thanks in advance for the help provided

Best

 

1 ACCEPTED SOLUTION

Hi @baptiste23 ,

 

The code:

Table.Transpose(Table.CombineColumns(Table.Transpose(Table.FirstN(#"Filtered Rows", 2)),{"Column1", "Column2"},Combiner.CombineTextByDelimiter(" - ", QuoteStyle.None),"Merged")),

 

Merges the 2 first rows in one, so you can handle the headers.

 

the code "Table.Skip(#"Filtered Rows", 2)" skips the first 2 rows (they are handled on the code above).

 

Table.Combine just appends them.



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

Proud to be a Super User!



View solution in original post

3 REPLIES 3
camargos88
Community Champion
Community Champion

Hi @baptiste23 ,

 

I'm not sure if it's that what you want...

 

let
Source = Excel.Workbook(File.Contents("D:\Downloads\data test.xlsx"), null, true),
#"Data sheet_Sheet" = Source{[Item="Data sheet",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(#"Data sheet_Sheet", [PromoteAllScalars=true]),
#"Transposed Table" = Table.Transpose(#"Promoted Headers"),
#"Filled Down" = Table.FillDown(#"Transposed Table",{"Column2"}),
#"Transposed Table1" = Table.Transpose(#"Filled Down"),
#"Removed Top Rows" = Table.Skip(#"Transposed Table1",1),
#"Filtered Rows" = Table.SelectRows(#"Removed Top Rows", each not Text.StartsWith([Column1], "Niveau")),
TableResult = Table.Combine(
{
Table.Transpose(Table.CombineColumns(Table.Transpose(Table.FirstN(#"Filtered Rows", 2)),{"Column1", "Column2"},Combiner.CombineTextByDelimiter(" - ", QuoteStyle.None),"Merged")),
Table.Skip(#"Filtered Rows", 2)
}
),
#"Promoted Headers1" = Table.PromoteHeaders(TableResult, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers1",{{"Identity - CLUB", type text}, {"Identity - Country", type text}, {"Identity - Type Of Club", type text}, {"Identity - Position", type text}, {"Identity - Number of Fans", type any}, {"Player & Staff developpement - TOTAL", type number}, {"Player & Staff developpement - First Team", Int64.Type}, {"Player & Staff developpement - Staff", type number}, {"Player & Staff developpement - U Teams", type number}, {"Player & Staff developpement - Talent evaluator", type number}, {"Technical facilities - TOTAL", type number}, {"Technical facilities - Training", type number}, {"Technical facilities - Stadium", Int64.Type}, {"Financial Assets - TOTAL", type number}, {"Financial Assets - Sales", type number}, {"Financial Assets - Costs", type any}})
in
#"Changed Type"

 

 

Or, if you just want to skip the first rows..just use the remove rows function.



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

Proud to be a Super User!



Hi @camargos88 

 

Thank you very much for the answer. It seems really interesting and I think I will be able to get hat I needed.

There is just one step that I don't understand:

 

TableResult = Table.Combine(
{
Table.Transpose(Table.CombineColumns(Table.Transpose(Table.FirstN(#"Filtered Rows", 2)),{"Column1", "Column2"},Combiner.CombineTextByDelimiter(" - ", QuoteStyle.None),"Merged")),
Table.Skip(#"Filtered Rows", 2)
}
)

 

Could you please give me more detail the transformation realised by this step so I can do it by myself?

 

Thank you for the help provided

Best

B

Hi @baptiste23 ,

 

The code:

Table.Transpose(Table.CombineColumns(Table.Transpose(Table.FirstN(#"Filtered Rows", 2)),{"Column1", "Column2"},Combiner.CombineTextByDelimiter(" - ", QuoteStyle.None),"Merged")),

 

Merges the 2 first rows in one, so you can handle the headers.

 

the code "Table.Skip(#"Filtered Rows", 2)" skips the first 2 rows (they are handled on the code above).

 

Table.Combine just appends them.



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

Proud to be a Super User!



Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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

Top Solution Authors