Data shaping/preparation - multiple dimensions in top 2 rows

[Anonymous]

Hi all,

Newbie here looking for support. I am working with data that comes through in the following form - unfortunately this is a template filled in by multiple parties so a challenge to change the source format. I want to reshape this so I end up with the following columns: Name, Category, Year, Price, Volume.

Suggestions on how to approach this appreciated. Thanks in advance.

		Price	Price	Price	Price	Volume	Volume	Volume	Volume
Product Name	Category	2016	2017	2018	2019	2016	2017	2018	2019
A	abc	$0.01	$0.01	$0.01	$0.01	12	12	12	12
B	def	$0.02	$0.02	$0.02	$0.02	13	13	13	13
C	ghi	$0.03	$0.03	$0.03	$0.03	14	14	14	14
D	jkl	$0.04	$0.04	$0.04	$0.04	15	15	15	15

[v-frfei-msft]

Hi @Anonymous,

Based on my test, we can shape the table in power query. Please refer to the picture as below to see the steps that I took.

And the M Code in the advanced editor for your reference.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCijKTylNLlHwS8xNVdJRck4sSU3PL6oEMo0MDM0glDmEsoBQlnjlYnWilRyBnMSkZCCpYqBnYIiHNjRCJ0D6nYDMlNQ0qDojPLShMToB0u8MZKZnZELVGeOhDU3QCZB+FyAzKzsHqs4ED21oik7ExgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, blank = _t, Price = _t, Price.1 = _t, Price.2 = _t, Price.3 = _t, Volume = _t, Volume.1 = _t, Volume.2 = _t, Volume.3 = _t]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Product Name", type text}, {"Category", type text}, {"2016", Currency.Type}, {"2017", Currency.Type}, {"2018", Currency.Type}, {"2019", Currency.Type}, {"2016_1", Int64.Type}, {"2017_2", Int64.Type}, {"2018_3", Int64.Type}, {"2019_4", Int64.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Product Name", "Category", "2016_1", "2017_2", "2018_3", "2019_4"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"2017_2", "2018_3", "2019_4"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"2016_1", "Volume"}, {"Attribute", "Year"}})
in
    #"Renamed Columns"

For more information, please check the pbix as attached.

https://www.dropbox.com/s/xfqwknd8ledkn47/%5DData%20shaping.pbix?dl=0

Regards,

Frank

[Anonymous]

@v-frfei-msft

Hi Frank,

Thanks a lot for that - actually quite straightforward when you know how! 

One thing though, apologies...I provided sub-optimal sample data, as I could (will) have different volumes in each year. In the pbix you shared, the second to last step removes 3 columns - actually I wouldn't want to do this as they would contain differing volumes per year.

I've played around with custom columns but can't quite get it right. Thoughts?

I can get it so the data is like this using conditional columns, but the problem here is that I would want to, for example, combine rows 1 & 5. How might I do that?