Transform Data : many occurences with the same code (a loop case?)

[chanal]

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

[Icey]

Hi @chanal ,

If @parry2k solved your problem, please consider to accept  his reply as a solution  to help the other members find it more quickly.

Best Regards,

Icey

[parry2k · Accepted solution]

@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"

[chanal]

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