Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I'm trying to collapse a single column of data into a comma separated string in power querty. I can do it with group by if first get a count (they are all ones as they are all unique values) but it adds extra steps that slow things down. I'm assuming I can get there from here with a single custom column. Essentially:
From:
System Name
ABC123
12345
2345XYZ
To:
ABC123 , 12345 , 2345XYZ
I'm doing a bunch of work to get the list down to something that is pasted into another system that "processes" the comma-delimited list. It is generally 600-1000 entries.
Solved! Go to Solution.
Hi @KENNEU, add this as new step (replace Source in my code with your previous step reference if needed):
= Text.Combine(Source[System Name], " , ")
Whole code:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRyNjQyVorViVYC0iamYBaIEREZpRQbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"System Name" = _t]),
Cobimed = Text.Combine(Source[System Name], " , ")
in
Cobimed
Hi @KENNEU, add this as new step (replace Source in my code with your previous step reference if needed):
= Text.Combine(Source[System Name], " , ")
Whole code:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRyNjQyVorViVYC0iamYBaIEREZpRQbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"System Name" = _t]),
Cobimed = Text.Combine(Source[System Name], " , ")
in
Cobimed
You can try the following code to see if it solves your concern...
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRyNjQyVorViVYC0iamYBaIEREZpRQbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"System Name" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"System Name", type text}}),
Custom1 = Table.FromList(Table.ToList(Table.Transpose(#"Changed Type"), Combiner.CombineTextByDelimiter(",")), Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Converted to Table" = Table.FromList(Custom1, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
#"Converted to Table"
Proud to be a Super User! | |
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.