Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Calling help from Pivoting Column Experts!

Hi guys, 

I've been wrapping my head around trying to pivot the table below (Current state). 
So far i have tried Grouping the field_name >> Adding Index >> Pivoting. However the id_user still contains multiple rows. Ideally it should only have one row. 

BR

J

Need Help
  • Ritaf1983's avatar
    Ritaf1983
    2 years ago

    Hi Anonymous 
    Select the field name column and pivot in the following way :

    Result :

    Pbix is attached

    If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

2 Replies

  • Ritaf1983's avatar
    Ritaf1983
    Super User
    2 years ago

    Hi Anonymous 
    Select the field name column and pivot in the following way :

    Result :

    Pbix is attached

    If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

  • moonstnew's avatar
    moonstnew
    Frequent Visitor
    2 years ago

    Hi Anonymous , Please use below M script to transform your data as mentioned above:

     

    let
    Source = Excel.Workbook(File.Contents("Your File Path"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"ID_User", Int64.Type}, {"Field_Name", type text}, {"Field_Values", type text}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Field_Name]), "Field_Name", "Field_Values")
    in
    #"Pivoted Column"

     

    If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.