Forum Discussion
Transforming Data in Power Query
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
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
Hi 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?
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"
That worked perfectly. Thank you !