Transforming Multiple Header file

[baptiste23]

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:

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

B 

[camargos88]

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.

[baptiste23]

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

[camargos88 · 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.