Forum Discussion
Transforming Data in Power Query
Hello,
I'm wondering if it's possible to transform the data from multiple columns into multiple rows, almost like a pivot table. See theexample below.
The data comes in like this:
I want to combine similar columns into one (highlighted in the same colors), but then show each item in different row. The image below is what I want it to look like.
Is that possible to do? Can someone show me the steps? I also just want to mention that this example is in small scale. My actual data set has about 40 columns and over a hundred rows.
Hi Shalom,
Result:
2 versions of code here. V1 is similar with AlienSx version. If I were you I would go for v2 because it is dynamic (no matter how many colum pairs do you have), but maybe you have to adjust v2_Extracted Text Before Delimiter because I don't know if you have space in every column name (as you have in example Dep 1, Dep 2 etc...). Of course it is possible to make dynamic v1 but I need to know your real column names.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUfLIz00FUiERQMLUysCgIBfICC4tKMjJTC0GMn0dgYSJlaEpRCIxByzqDBI1NLIyBqmP1YlWMkKSdIwCEuZWJhAtfo7BIMX+HsgWuGXmJeYlwy02hIiDDDJGkQXbYw7T5QhyFYo+QysjiJR3ZklyRmoeyD4XIGEMcXBsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Item = _t, #"Dep 1" = _t, #"State 1" = _t, #"Time 1" = _t, #"Dep 2" = _t, #"State 2" = _t, #"Time 2" = _t, #"Dep 3" = _t, #"State 3" = _t, #"Time 3" = _t]), v1_ListTransformMany = List.TransformMany(List.Buffer(Table.ToRows(Source)), each List.Split(List.Skip(_), 3), (x,y)=> {x{0}} & y ), v1_ToTable = Table.FromRows(v1_ListTransformMany, {"Item", "Dep", "State", "Time"}), StepBack = Source, #"v2_Unpivoted Other Columns" = Table.UnpivotOtherColumns(StepBack, {"Item"}, "Column Name", "Value"), #"v2_Extracted Text Before Delimiter" = Table.TransformColumns(#"v2_Unpivoted Other Columns", {{"Column Name", each Text.BeforeDelimiter(_, " "), type text}}), v2_GroupedRows = Table.Group(#"v2_Extracted Text Before Delimiter", {"Column Name"}, {{"All", each _, type table}, {"List", each Table.ToColumns(Table.SelectColumns(_, List.Select(Table.ColumnNames(_), each _ <> "Column Name"))), type list}}), v2_Zip = List.Zip(v2_GroupedRows[List]), v2_ToTable = Table.FromColumns({v2_Zip{0}{0}} & List.Skip(v2_Zip){0}, {"Item"} & v2_GroupedRows[Column Name]) in v2_ToTable
5 Replies
- AlienSx
Super User
hello, Shalom
let Source = your_table, rows = List.Buffer(Table.ToRows(Source)), tra = List.TransformMany( rows, (x) => List.Zip( {List.Skip(List.Alternate(x, 2, 1, 2)), List.Alternate(x, 2, 1, 0), List.Skip(List.Alternate(x, 2, 1, 1))} ), (x, y) => {x{0}} & y ), z = Table.FromRows(tra, {"Item", "Dep", "State", "Time"}) in z - dufoq3
Community Champion
Hi Shalom,
Result:
2 versions of code here. V1 is similar with AlienSx version. If I were you I would go for v2 because it is dynamic (no matter how many colum pairs do you have), but maybe you have to adjust v2_Extracted Text Before Delimiter because I don't know if you have space in every column name (as you have in example Dep 1, Dep 2 etc...). Of course it is possible to make dynamic v1 but I need to know your real column names.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUfLIz00FUiERQMLUysCgIBfICC4tKMjJTC0GMn0dgYSJlaEpRCIxByzqDBI1NLIyBqmP1YlWMkKSdIwCEuZWJhAtfo7BIMX+HsgWuGXmJeYlwy02hIiDDDJGkQXbYw7T5QhyFYo+QysjiJR3ZklyRmoeyD4XIGEMcXBsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Item = _t, #"Dep 1" = _t, #"State 1" = _t, #"Time 1" = _t, #"Dep 2" = _t, #"State 2" = _t, #"Time 2" = _t, #"Dep 3" = _t, #"State 3" = _t, #"Time 3" = _t]), v1_ListTransformMany = List.TransformMany(List.Buffer(Table.ToRows(Source)), each List.Split(List.Skip(_), 3), (x,y)=> {x{0}} & y ), v1_ToTable = Table.FromRows(v1_ListTransformMany, {"Item", "Dep", "State", "Time"}), StepBack = Source, #"v2_Unpivoted Other Columns" = Table.UnpivotOtherColumns(StepBack, {"Item"}, "Column Name", "Value"), #"v2_Extracted Text Before Delimiter" = Table.TransformColumns(#"v2_Unpivoted Other Columns", {{"Column Name", each Text.BeforeDelimiter(_, " "), type text}}), v2_GroupedRows = Table.Group(#"v2_Extracted Text Before Delimiter", {"Column Name"}, {{"All", each _, type table}, {"List", each Table.ToColumns(Table.SelectColumns(_, List.Select(Table.ColumnNames(_), each _ <> "Column Name"))), type list}}), v2_Zip = List.Zip(v2_GroupedRows[List]), v2_ToTable = Table.FromColumns({v2_Zip{0}{0}} & List.Skip(v2_Zip){0}, {"Item"} & v2_GroupedRows[Column Name]) in v2_ToTableHi dufoq3,
I'm still new to power query so I need a little help understanding the code. I have a couple of questions. I'm going with V2, so I assume all I have to do is leave out the two V1 code lines in your solution.
1. The green text inbetween the quotation marks "i45..AA=", is this where my source table goes or should I use the text as is? If I use the text as is, at what point do I reference the query/table that has my data in it?2. On the V2 unpivoted other columns code, my understanding is that I should insert the previous step name into the "StepBack" portion. But I'm not too sure what the previous step name would be since the step before that is the Source = line
3. I understand that I will have to replace "Item","Dep","State" and "Time" with my actual column names, what do I replace the "List" with or do I leave that like that as well?
- dufoq3
Shalom, you have to turn on Query Settings (but it should be turned on by default)
Then in right query settings panel you caj X out both v1 steps and also StepBack
Then in Source step delete whole code and refer to your query (i.e. if your query name is MyData so replace whole code to = MyData, but don't forget if you have space or some spacial characters in name then the reference could be like this: #"My Data"
