Forum Discussion
Moving Data Back to List
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]
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],",")
4 Replies
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)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],",")
