Moving Data Back to List

[NickTT]

I have data in the following Format:

Form Number	State
0001	New York
0001	Delaware
0001	Maryland
0002	New York
0002	Delaware
0003	Maryland

I would like to return it in a report for easier viewing to a single line likes this using the Metrix or Table Visualization.

Form	State
0001	Deleware, Maryland, New York
0002	Delaware, New York
0003	Maryland

This data is coming from a SharePoint List where State is managed by a Managed TermSet. This is also a gross simplification of the data. I have multiple lookup colums like this is my dataset. Expanding each column is created extra rows of data to work with makes the model larger than it really needs to be. So I am trying to flatten the dataset as much as possible.

Extract Values from List step would be perfect here but unfortunately the values are then stored as a Record and need to be extracted again. So instead you get the following Error:

Expression.Error: We cannot convert a value of type Record to type Text.
Details:
    Value=
        Label=Maryland
        TermGuid=d6b08820-07d2-412e-8eac-f3058cb6068d
        WssId=1
    Type=[Type]

[Greg_Deckler · Accepted solution]

@NickTT - In DAX, you could put Form Number in a table visual and then this measure in the table visual as well.

Measure = CONCATENATEX(DISTINCT('Table'[State]),[State],",")

[NickTT]

This DAX Measure almost does what I want but I still get Multiple entries for some data or no results for other. Trying to figure out why.

[NickTT]

Issue was Filter Direction. Had to change it to "Both". Very cleaver and clean solution there!

[FrankAT]

Hi @NickTT 

you can do it in Power Query like this:

// Table
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjAwMFTSUfJLLVeIzC/KVorVgYu5pOYklicWpSKL+SYWVeYk5qXAxIyw6DXCotcYRW8sAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Form Number" = _t, State = _t]),
    #"Grouped Rows" = Table.Group(Source, {"Form Number"}, {{"Count", each _, type table [Form Number=nullable text, State=nullable text]}}),
    #"Added Custom1" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.RemoveColumns([Count],"Form Number")),
    #"Added Custom" = Table.AddColumn(#"Added Custom1", "State", each Table.ToList([Custom])),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"Count", "Custom"}),
    #"Extracted Values" = Table.TransformColumns(#"Removed Columns1", {"State", each Text.Combine(List.Transform(_, Text.From), ","), type text})
in
    #"Extracted Values"

With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)