Hi,
I have a file that contains sales and margin data by country and vendor. The data for each country is currently in separate columns. The first row of the data shows the country code. The second the type of data, Sales, margin, margin % etc. It then lists the values for each vendor.
I need to get the data in to a format where I can easily pivot it. I need to get each countries data in to the rows but can't work out how to do this with power query with the data in the existing format.
I have attached an extract from the file. In the full version there are around 20 countries and 100 vendors.
Any help appreciated.
Thanks
Bax
Create a table from your data, make sure "My table has headers" check box is NOT checked.
Use these queries (first query name is Headers):
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Kept First Rows" = Table.FirstN(Source,2),
#"Transposed Table" = Table.Transpose(#"Kept First Rows"),
#"Merged Columns" = Table.CombineColumns(#"Transposed Table",{"Column1", "Column2"},Combiner.CombineTextByDelimiter("-", QuoteStyle.None),"Merged"),
#"Transposed Table1" = Table.Transpose(#"Merged Columns")
in
#"Transposed Table1"
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Removed Top Rows" = Table.Skip(Source,2),
Custom1 = Table.Combine({Headers,#"Removed Top Rows"}),
#"Promoted Headers" = Table.PromoteHeaders(Custom1, [PromoteAllScalars=true]),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Promoted Headers", {"Country-Vendor"}, "Attribute", "Value")
in
#"Unpivoted Other Columns"
Hi Catalin,
That worked fine. I also then split the Attribute column at the "-" so I get the country code as a separate column. Just what I needed.
Thanks for your help.
Cheers
Bax
