Help Transforming Data

[shaebert]

I have a data set that looks like this:

Looking to transform the data into this:

Thanks for helping! 

[v-angzheng-msft · Accepted solution]

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.

[littlemojopuppy]

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

[AllisonKennedy]

@shaebert  is this close enough: 

Topic Summary =

CONCATENATEX(RawData, RawData[Topic] & "-" & RawData[Topic Score], unichar(10) )