merge rows

[JP-Ronse]

Hi,

A bookkeeping application generates an export that looks as:

What I need is:

Is this possible?

Kind regards,

JP-Ronse

[MarcelBeug]

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"

[Zubair_Muhammad]

@JP-Ronse

[MarcelBeug · Accepted 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).

[Zubair_Muhammad]

HI @JP-Ronse

Try this.

First Use the Query Editor to fill down the Blank Cells

[Zubair_Muhammad]

@JP-Ronse

Now you can use this MEASURE to concatenate the HEAD 2

Head_2 Measure =
IF (
    HASONEVALUE ( Table1[Head_1] ),
    CONCATENATEX ( Table1, Table1[Head_2], " " )
)

[JP-Ronse]

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

[Zubair_Muhammad]

@JP-Ronse

You can add it from the Modelling Tab or by right clicking the Table fields

The picture below shows this

[JP-Ronse]

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

[MarcelBeug]

Luckily you still have the recommended Power Query solution.

[JP-Ronse]

Dag Marcel,

Can you recommend some reading to get more familiar with PQ?

Prettig eindejaarsfeest.

JP-Ronse

[MarcelBeug]

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/

[JP-Ronse]

Thanks.