Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hello,
I've struggling for days with this and can't get it right, please assist.
I want to merge dynamically earch 2 columns in the table, the number of columns are changing and that's why
this is the column
and here is it after tranposing the table
and this is the expected output, each 2 columns merged with comma delimited
appreciate any help
Thanks.
Solved! Go to Solution.
Hi @hiken ,
I would do it like so:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjRU0lFyNAIRxiDCRClWJ1rJCSTqBBJ1Aok6QUSdQaLOIFFnkKgzUDQWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}}),
AddFieldValues = Table.AddColumn(#"Changed Type", "FieldValues", each Record.FieldValues(_)),
AddPairs = Table.AddColumn(AddFieldValues, "Pairs", each {0..(List.Count([FieldValues])/2)-1}),
AddCombinations = Table.AddColumn(AddPairs, "Combinations", each List.Transform([Pairs], (l)=> Text.Combine(List.Range([FieldValues],(l*2),2), ", "))),
CreateNewTable = Table.FromRows( AddCombinations[Combinations] )
in
CreateNewTable
Please also check the file enclosed.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Hi @hiken ,
I would do it like so:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjRU0lFyNAIRxiDCRClWJ1rJCSTqBBJ1Aok6QUSdQaLOIFFnkKgzUDQWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}}),
AddFieldValues = Table.AddColumn(#"Changed Type", "FieldValues", each Record.FieldValues(_)),
AddPairs = Table.AddColumn(AddFieldValues, "Pairs", each {0..(List.Count([FieldValues])/2)-1}),
AddCombinations = Table.AddColumn(AddPairs, "Combinations", each List.Transform([Pairs], (l)=> Text.Combine(List.Range([FieldValues],(l*2),2), ", "))),
CreateNewTable = Table.FromRows( AddCombinations[Combinations] )
in
CreateNewTable
Please also check the file enclosed.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.