Forum Discussion
merge rows
Hi,
A bookkeeping application generates an export that looks as:
What I need is:
Is this possible?
Kind regards,
JP-Ronse
Conceptually, queries shape unstructured data into tables. Next, DAX (and visuals) may kick in for further data analysis, like aggregation and (filter) context sensitive calculations.
So, in this specific case, I would recommend a Power Query solution, even though it can also be done with DAX (in combination with visuals).
12 Replies
In the Query Editor, make sure the table columns are of type text, then fill down the first column, group by the first column, specifying a dummy operation for Head_2 (e.g. Max) and then adjust the generated code to have the text combined:
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Head_1", type text}, {"Head_2", type text}}), #"Filled Down" = Table.FillDown(#"Changed Type",{"Head_1"}), #"Grouped Rows" = Table.Group(#"Filled Down", {"Head_1"}, {{"Head_2", each Text.Combine(_[Head_2]," "), type text}}) in #"Grouped Rows"Conceptually, queries shape unstructured data into tables. Next, DAX (and visuals) may kick in for further data analysis, like aggregation and (filter) context sensitive calculations.
So, in this specific case, I would recommend a Power Query solution, even though it can also be done with DAX (in combination with visuals).
Now you can use this MEASURE to concatenate the HEAD 2
Head_2 Measure = IF ( HASONEVALUE ( Table1[Head_1] ), CONCATENATEX ( Table1, Table1[Head_2], " " ) )Hi Zubair_Muhammad,
Thanks for the swift reply! I am a learning newby with a long way to go (I'm afraid). Can you explain how to add a Measure?
Kind regards,
JP-Ronse
