Forum Discussion

shaebert's avatar
shaebert
Helper III
4 years ago
Solved

Help Transforming Data

I have a data set that looks like this:

 

Looking to transform the data into this:

 

Thanks for helping! 

Need Help
  • v-angzheng-msft's avatar
    v-angzheng-msft
    4 years ago

    Hi, shaebert 

    Result:

    M code:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ3NNI3MjAyUtJR8srPyCvOzwOyQvILMpMVDIEsUwsg4ZdaUp5flJ2Zl64Uq4NfD0jM2JI0PcYge0xJ02MCZFkakKYH5B8zcyDhkliSSFA1yEHmOFUH52aWZKCotTAlTq0ZyLdmxKkF+dLYGM2XsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Date " = _t, #" Name " = _t, Topic = _t, #"Topic Score" = _t, Group = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date ", type date}, {" Name ", type text}, {"Topic", type text}, {"Topic Score", Int64.Type}, {"Group", type text}}),


    
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Date ", " Name ", "Group"}, {{"GroupTable", each Table.AddColumn(Table.SelectColumns(_,{"Topic","Topic Score"}),"Summary", each [Topic]&"-"&Text.From([Topic Score]))
}}
),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Summary", each List.Accumulate([GroupTable][Summary],"Group: "&_[Group],(state, current) => state&"#(lf)"&current)
),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Group", "GroupTable"})
in
    #"Removed Columns"

    Please refer to the attachment below for details.

    Hope this helps.

     

     

    Best Regards,
    Community Support Team _ Zeon Zheng


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

3 Replies

  • AllisonKennedy's avatar
    AllisonKennedy
    Community Champion
    4 years ago

    shaebert  is this close enough: 

     

    Topic Summary =

    CONCATENATEX(RawData, RawData[Topic] & "-" & RawData[Topic Score], unichar(10) )
  • littlemojopuppy's avatar
    littlemojopuppy
    Community Champion
    4 years ago

    shaebert you have data that is properly laid out in a table that doesn't violate first normal form (no repeating values).  And you want to break 1NF???  Why would you want to do that?  Any manipulation you might need to do would become far more difficult than it needs to be.

    What you could do instead - without transforming anything - is present the data in a matrix visual with values shown on rows.

     

    Just a thought...

  • v-angzheng-msft's avatar
    v-angzheng-msft
    Community Support
    4 years ago

    Hi, shaebert 

    Result:

    M code:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ3NNI3MjAyUtJR8srPyCvOzwOyQvILMpMVDIEsUwsg4ZdaUp5flJ2Zl64Uq4NfD0jM2JI0PcYge0xJ02MCZFkakKYH5B8zcyDhkliSSFA1yEHmOFUH52aWZKCotTAlTq0ZyLdmxKkF+dLYGM2XsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Date " = _t, #" Name " = _t, Topic = _t, #"Topic Score" = _t, Group = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date ", type date}, {" Name ", type text}, {"Topic", type text}, {"Topic Score", Int64.Type}, {"Group", type text}}),


    
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Date ", " Name ", "Group"}, {{"GroupTable", each Table.AddColumn(Table.SelectColumns(_,{"Topic","Topic Score"}),"Summary", each [Topic]&"-"&Text.From([Topic Score]))
}}
),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Summary", each List.Accumulate([GroupTable][Summary],"Group: "&_[Group],(state, current) => state&"#(lf)"&current)
),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Group", "GroupTable"})
in
    #"Removed Columns"

    Please refer to the attachment below for details.

    Hope this helps.

     

     

    Best Regards,
    Community Support Team _ Zeon Zheng


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