Unable to transform this simple data into the below Layout.

[iamprajot]

Hi all
I am unable to achive this simple data transformation. Please help asap.

Link to the Data folder (Data.xlsx & Data.pbix).

I want to change the layout of Table A to Table B as shown below.

Basically I want to move Item 1 and D into rows before Dimention and for same should go for the following data (Item 2...3...4) to APPEND below as shown in Table B.

Table A

						Item 1						Item 2
						D						D
Serial	Province	City	Store Code	Coverage	Height	Dimension	Qty	Area	Coverage Calculation	Material	Subtotal	Dimension	Qty	Area	Coverage Calculation	Material	Subtotal
1	Quebec	Témiscamingue	2715			0.174*0.614	6	0.106836	0.641016	Paper	9.616345	0.21*0.297	1	0.06237	0.06237	Paper	2.977544
2	Nova Scotia	Cape Breton	2732			0.174*0.614	6	0.106836	0.641016	Paper	9.616345	0.21*0.297	1	0.06237	0.06237	Paper	2.977544
3	British Columbia	Fraser-Fort George	2767			0.174*0.614	6	0.106836	0.641016	Paper	9.616345	0.21*0.297	1	0.06237	0.06237	Paper	2.977544
4	Ontario	Algoma	2765			0.174*0.614	6	0.106836	0.641016	Paper	9.616345	0.21*0.297	1	0.06237	0.06237	Paper	2.977544
5	New Brunswick	Northumberland	2761			0.174*0.614	6	0.106836	0.641016	Paper	9.616345	0.21*0.297	1	0.06237	0.06237	Paper	2.977544

Table B

Serial	Province	City	Store Code	Coverage	Height	COLUMN A	COLUMN B	Dimension	Qty	Area	Coverage Calculation	Material	Subtotal
1	Quebec	Témiscamingue	2715			Item 1	C	0.174*0.614	6	0.106836	0.641016	Paper	9.6163452
2	Nova Scotia	Cape Breton	2732			Item 1	C	0.174*0.614	6	0.106836	0.641016	Paper	9.6163452
3	British Columbia	Fraser-Fort George	2767			Item 1	C	0.174*0.614	6	0.106836	0.641016	Paper	9.6163452
4	Ontario	Algoma	2765			Item 1	C	0.174*0.614	6	0.106836	0.641016	Paper	9.6163452
5	New Brunswick	Northumberland	2761			Item 1	C	0.174*0.614	6	0.106836	0.641016	Paper	9.6163452
1	Quebec	Témiscamingue	2715			Item 2	D	0.21*0.297	1	0.06237	0.06237	Paper	2.9775438
2	Nova Scotia	Cape Breton	2732			Item 2	D	0.21*0.297	1	0.06237	0.06237	Paper	2.9775438
3	British Columbia	Fraser-Fort George	2767			Item 2	D	0.21*0.297	1	0.06237	0.06237	Paper	2.9775438
4	Ontario	Algoma	2765			Item 2	D	0.21*0.297	1	0.06237	0.06237	Paper	2.9775438
5	New Brunswick	Northumberland	2761			Item 2	D	0.21*0.297	1	0.06237	0.06237	Paper	2.9775438

[v-shex-msft · Accepted solution]

HI @iamprajot ,

You can take a look at following pbix file to get transformed table formula.

let
    Source = Excel.Workbook(File.Contents("C:\Users\xiaoxish\Downloads\Data.xlsx"), null, true),
    Data_Sheet = Source{[Item="Data",Kind="Sheet"]}[Data],
    #"Removed Columns"=Table.RemoveColumns(Table.FillDown(Table.Transpose(Table.SelectColumns(Data_Sheet,{"Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17", "Column18"})),{"Column1"}),{"Column2"}),
    Custom1 = Table.AddColumn(Table.PromoteHeaders(Table.RemoveFirstN(Table.SelectColumns(Data_Sheet,{"Column1", "Column2", "Column3", "Column4", "Column5", "Column6"}),2)),"Custom",each Table.ExpandTableColumn(Table.Group(#"Removed Columns", {"Column1"}, {{"Count", each Table.Distinct(Table.PromoteHeaders(Table.Transpose(Table.RemoveColumns(_,"Column1")))), type table }}), "Count", {"Dimension", "Qty", "Area", "Coverage Calculation", "Material", "Subtotal"}, {"Dimension", "Qty", "Area", "Coverage Calculation", "Material", "Subtotal"})),
    #"Expanded Custom" = Table.ExpandTableColumn(Custom1, "Custom", {"Column1", "Dimension", "Qty", "Area", "Coverage Calculation", "Material", "Subtotal"}, {"Column1", "Dimension", "Qty", "Area", "Coverage Calculation", "Material", "Subtotal"})
in
    #"Expanded Custom"

Notice: it is hard complex to use power query functions to format pivot table, if you can please do these on excel worksheet side. (It should more simple to do)

Regards,

Xiaoxin Sheng

[iamprajot]

I sent the file with 2 Item categories but I have 100 of them.
Can you also help in what I should change it to expand it for those many.
And the requirement is to do this in PBI and client don't want it in excel.

[iamprajot]

I managed to do it for rest of the 100 categories and to be exact all the data was in chinese so it took me long enough to finish but thanks again for helping me.