Forum Discussion
Transform Data : many occurences with the same code (a loop case?)
Hello,
I would like to solve one problem I encounter when I have this file : https://bit.ly/2r7wVug
I would like to transform in M those datas but there are some loop occurrences :
Each time the code S1 appears, it concerns another employee. I would like to have one row for each employee and the different codes in columns.
Question : How do you think I should do ? Increment a different index for each new employee ? Would you do that in M with which formula?
Thank you so much for your help
Isabelle
chanal start a blank query and copy following code and you will get the result and take it from there
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCjZU0lEK9vUM8VCK1QFyjYBcx5TEXAjPGMgzNDXUMzOH8E1AfB0TAwOFR01rIEIg/c4+jpEI7cGJSUWZeYn4TDCFmRALAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Code = _t, Values = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Code", type text}, {"Values", type text}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Code"}, {{"Rank", each Table.AddIndexColumn(_, "Rank", 1)}}), #"Expanded Rank" = Table.ExpandTableColumn(#"Grouped Rows", "Rank", {"Values", "Rank"}, {"Values", "Rank.1"}), #"Pivoted Column" = Table.Pivot(#"Expanded Rank", List.Distinct(#"Expanded Rank"[Code]), "Code", "Values") in #"Pivoted Column"
3 Replies
chanal start a blank query and copy following code and you will get the result and take it from there
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCjZU0lEK9vUM8VCK1QFyjYBcx5TEXAjPGMgzNDXUMzOH8E1AfB0TAwOFR01rIEIg/c4+jpEI7cGJSUWZeYn4TDCFmRALAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Code = _t, Values = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Code", type text}, {"Values", type text}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Code"}, {{"Rank", each Table.AddIndexColumn(_, "Rank", 1)}}), #"Expanded Rank" = Table.ExpandTableColumn(#"Grouped Rows", "Rank", {"Values", "Rank"}, {"Values", "Rank.1"}), #"Pivoted Column" = Table.Pivot(#"Expanded Rank", List.Distinct(#"Expanded Rank"[Code]), "Code", "Values") in #"Pivoted Column"Dear Parry,
Thank you so much for your quick answer !! It really works with my example, thank you.
My file is a little bit more complex than my example, so I may ask you another question later.
Best regards,
Isabelle
