Forum Discussion
Unable to transform this simple data into the below Layout.
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 |
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
3 Replies
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
- 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.- 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.
