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.
Hi,
A bookkeeping application generates an export that looks as:
What I need is:
Is this possible?
Kind regards,
JP-Ronse
Solved! Go to Solution.
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).
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).
HI @JP-Ronse
Try this.
First Use the Query Editor to fill down the Blank Cells
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
You can add it from the Modelling Tab or by right clicking the Table fields
The picture below shows this
Hi Zubair_Muhammad,
I am using excel 2013 with the Power Query add-in and I don't find this option. I tried with a custom column but it doen't accept the formula. Maybe it is just not possible with my configuration.
Kind regards,
JP-Ronse
Luckily you still have the recommended Power Query solution.
Dag Marcel,
Can you recommend some reading to get more familiar with PQ?
Prettig eindejaarsfeest.
JP-Ronse
Dankjewel, insgelijks. (Thanks, likewise)
With regard to readings: sometimes I publish video's about specific Power Query subjects on my YouTube channel and I recently published a Power Query (M) Functions dashboard.
Otherwise you can find learning resources on http://www.thebiccountant.com/learning-resources/
Thanks.
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.
User | Count |
---|---|
104 | |
96 | |
79 | |
65 | |
62 |
User | Count |
---|---|
147 | |
116 | |
104 | |
88 | |
65 |