Forum Discussion
Move row data to existing column
Perhaps a basic question, but I can't find a way to achieve this.
I have the following data table:
| 2019 | 2020 | |||||||||
| 1 | 2 | 3 | 4 | 1 | ||||||
| Location | Sales | COS | Sales | COS | Sales | COS | Sales | COS | Sales | COS |
| X | 10 | 9 | 8 | 7 | 10 | 9 | 8 | 7 | 13 | 11 |
| Y | 12 | 10 | 12 | 10 | 11 | 9 | 12 | 10 | 14 | 12 |
| Z | 16 | 12 | 16 | 13 | 16 | 12 | 15 | 10 | 14 | 11 |
I would like to adjust the table in such a way that it shown as follows:
| Location | Year | Period | Sales | COS |
| X | 2019 | 01 | 10 | 9 |
| X | 2019 | 02 | 8 | 7 |
| X | 2019 | 03 | 10 | 9 |
| X | 2019 | 04 | 8 | 7 |
| X | 2020 | 01 | 13 | 11 |
| Y | 2019 | 01 | 12 | 10 |
| Y | 2019 | 02 | 12 | 10 |
| Y | 2019 | 03 | 11 | 9 |
| y | 2019 | 04 | 12 | 10 |
| Y | 2020 | 01 | 14 | 12 |
| Z | 2019 | 01 | 16 | 12 |
| Z | 2019 | 02 | 16 | 13 |
| Z | 2019 | 03 | 16 | 12 |
| Z | 2019 | 04 | 15 | 10 |
| Z | 2020 | 01 | 14 | 11 |
How can I do this in PowerBI?
Hi Anonymous ,
Try this new code:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nU/BDoIwDP0VwpkDHTDgqJh4MdGEi7pwIOiBxDiD8P+2o5sjUQ8ma1772tfXKRUGYRTiEzGUCMGXMBMipqyJlKOBUTAmjOmiT4qd7tqx13ckDoO+TN0YbAc9PbCu29v1iVjt678rsjhiviJTOpM+U2DkHwm6E8Cp1ny64NMtJl4990lxsj7C7vYyYCuPSufSSisMyYvNAF2VsUvBjdy5nVkC0m2V7gtvLlvagdNufJsfSWlcm+YF", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t, Column8 = _t, Column9 = _t, Column10 = _t, Column11 = _t, Column12 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}}),
#"Transposed Table" = Table.Transpose(#"Changed Type"),
#"Trimmed Text" = Table.TransformColumns(#"Transposed Table",{{"Column1", Text.Trim, type text}, {"Column2", Text.Trim, type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Trimmed Text","",null,Replacer.ReplaceValue,{"Column1", "Column2"}),
#"Filled Down" = Table.FillDown(#"Replaced Value",{"Column1", "Column2"}),
Table1 = Table.RemoveColumns(Table.FirstN(#"Filled Down", 2), {"Column1", "Column2"}),
#"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Column2] <> null)),
#"Grouped Rows" = Table.Group(#"Filtered Rows", {"Column1", "Column2"}, {{"Rows", each Table.PromoteHeaders(Table.Transpose(Table.Combine({
Table.RemoveColumns(_, {"Column1", "Column2"}),
Table1 }))), type table [Sales=text, COS=text, Location=text, #"Product Group"=text]}}),
#"Expanded Rows" = Table.ExpandTableColumn(#"Grouped Rows", "Rows", {"Sales", "COS", "Location", "Product Group"}, {"Sales", "COS", "Location", "Product Group"}),
#"Renamed Columns" = Table.RenameColumns(#"Expanded Rows",{{"Column1", "Year"}, {"Column2", "Period"}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Year", Int64.Type}, {"Period", Int64.Type}, {"Sales", Int64.Type}, {"COS", Int64.Type}, {"Location", type text}, {"Product Group", type text}})
in
#"Changed Type1"
5 Replies
camargos88Community Champion
Hi Anonymous ,
Go to Query Editor and create a blank query, paste this m code on the Advanced Editor:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwtFTSUVLAg4HIyMDIAMyL1YkGixlC5YygtDGUNoHShnDVPvnJiSWZ+XlAgeDEnNRiIO3sH0w2D2RkBMgCkHtALrcAYnOsAiA3GRqCdUSCmEYwVUgsQ6gmJCETCBekLQrENINLm8FNRYiZouoDWhcLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t, Column8 = _t, Column9 = _t, Column10 = _t, Column11 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}}),
#"Transposed Table" = Table.Transpose(#"Changed Type"),
#"Trimmed Text" = Table.TransformColumns(#"Transposed Table",{{"Column1", Text.Trim, type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Trimmed Text","",null,Replacer.ReplaceValue,{"Column1"}),
#"Filled Down" = Table.FillDown(#"Replaced Value",{"Column1", "Column2"}),
#"Trimmed Text1" = Table.TransformColumns(#"Filled Down",{{"Column2", Text.Trim, type text}}),
#"Replaced Value1" = Table.ReplaceValue(#"Trimmed Text1","",null,Replacer.ReplaceValue,{"Column2"}),
#"Filled Down1" = Table.FillDown(#"Replaced Value1",{"Column2"}),
#"Promoted Headers" = Table.PromoteHeaders(#"Filled Down1", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"2019", Int64.Type}, {"Column2", Int64.Type}, {"Location", type text}, {"X", Int64.Type}, {"Y", Int64.Type}, {"Z", Int64.Type}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"2019", "Year"}, {"Column2", "Period"}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Renamed Columns", {"Year", "Period", "Location"}, "Attribute", "Value"),
#"Pivoted Column" = Table.Pivot(#"Unpivoted Other Columns", List.Distinct(#"Unpivoted Other Columns"[Location]), "Location", "Value"),
#"Renamed Columns1" = Table.RenameColumns(#"Pivoted Column",{{"Attribute", "Location"}})
in
#"Renamed Columns1"Thanks, that helped a lot! My data table does however has a bit more data. Is the same possible with the following table?
2019 2020 1 2 3 4 1 Location Product Group Sales COS Sales COS Sales COS Sales COS Sales COS X A 10 9 8 7 10 9 8 7 13 11 X B 3 2 4 2 4 3 4 2 3 2 Y A 12 10 12 10 11 9 12 10 14 12 Y C 6 4 10 8 5 2 8 4 7 2 Z C 16 12 16 13 16 12 15 10 14 11 Z D 10 8 10 8 10 8 10 8 9 7 Location Product Group Year Period Sales COS X A 2019 1 10 9 X A 2019 2 8 7 X A 2019 3 10 9 X A 2019 4 8 7 X A 2020 1 13 11 X B 2019 1 3 2 X B 2019 2 4 2 etc. - camargos88
Hi Anonymous ,
Try this one:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nU/BDoIwDP0VwpkDHTDgqJh4MdGEi7pwIOiBxDiD8P+2o5sjUQ8ma1772tfXKRUGYRTiEzGUCMGXMBMipqyJlKOBUTAmjOmiT4qd7tqx13ckDoO+TN0YbAc9PbCu29v1iVjt678rsjhiviJTOpM+U2DkHwm6E8Cp1ny64NMtJl4990lxsj7C7vYyYCuPSufSSisMyYvNAF2VsUvBjdy5nVkC0m2V7gtvLlvagdNufJsfSWlcm+YF", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t, Column8 = _t, Column9 = _t, Column10 = _t, Column11 = _t, Column12 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}}),
#"Transposed Table" = Table.Transpose(#"Changed Type"),
#"Trimmed Text" = Table.TransformColumns(#"Transposed Table",{{"Column1", Text.Trim, type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Trimmed Text","",null,Replacer.ReplaceValue,{"Column1"}),
#"Filled Down" = Table.FillDown(#"Replaced Value",{"Column1", "Column2"}),
#"Trimmed Text1" = Table.TransformColumns(#"Filled Down",{{"Column2", Text.Trim, type text}}),
#"Replaced Value1" = Table.ReplaceValue(#"Trimmed Text1","",null,Replacer.ReplaceValue,{"Column2"}),
#"Filled Down1" = Table.FillDown(#"Replaced Value1",{"Column2"}),
#"Added Custom" = Table.AddColumn(#"Filled Down1", "Custom", each let _year = [Column1], _month = [Column2] in
Table.PromoteHeaders(
Table.Transpose(
Table.RemoveColumns(Table.SelectRows(#"Filled Down1", each
([Column1] = _year and [Column2] = _month) or
[Column2] = null), {"Column1", "Column2"})))),
#"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Column2] <> null)),
#"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Column1", "Column2", "Custom"}),
#"Removed Duplicates" = Table.Distinct(#"Removed Other Columns", {"Column1", "Column2"}),
#"Expanded Custom" = Table.ExpandTableColumn(#"Removed Duplicates", "Custom", {"Location", "Product Group", "Sales", "COS"}, {"Location", "Product Group", "Sales", "COS"}),
#"Renamed Columns" = Table.RenameColumns(#"Expanded Custom",{{"Column1", "Year"}, {"Column2", "Period"}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Year", Int64.Type}, {"Period", Int64.Type}, {"Location", type text}, {"Product Group", type text}, {"Sales", Int64.Type}, {"COS", Int64.Type}}),
#"Filtered Rows1" = Table.SelectRows(#"Changed Type1", each ([Location] = "X"))
in
#"Filtered Rows1"