Forum Discussion
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:
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
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.
3 Replies
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.
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.