Concat rows in PowerQuery

[TanzilHasan]

Hi Good people, 

I'm new to the Power BI world and I'm working and learning at the same time. I want to concatenate the first two rows while keeping the other columns intact. This is easy to do in Excel, but I want to achieve this in PowerQuery so that I can use it on a larger scale with a refresh. I have attached the table here.

What I want to do is concatenate the first two rows of columns D, E, and F. I would expect the values would be like this: Q1 AA, Q2 FG, Q3 HL in first row.

Can anyone please guide me on achieving this in PowerQuery in Power BI?

Thanks in advance.

My current data format:

Start Date	End Date	ID	Q1	Q2	Q3	Current Page
Start Date	End Date	ID	AA	FG	HL	Current Page
1/05/2024	1/05/2024	123	2	5	10	Brand

My expected format is this:

Start Date	End Date	ID	Q1 AA	Q2 FG	Q3 HL	Current Page
1/05/2024	1/05/2024	123	2	5	10	Brand

[Ashish_Mathur · Accepted solution]

Hi,

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Added Custom" = Record.ToTable(Table.AddColumn(Source, "Custom", each _){0}),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Name] <> "Custom")),
    #"Added Custom1" = Table.AddColumn(#"Filtered Rows", "Custom", each if Text.StartsWith([Name],"Q",Comparer.OrdinalIgnoreCase) then [Name]&" "&[Value] else [Name]),
    Custom1 = Table.FromRows(Table.ToRows(Table.Skip(Source,1)),#"Added Custom1"[Custom])
in
    Custom1

Hope this helps.

[TanzilHasan]

Thank you so much, it works. 

[Ashish_Mathur]

You are welcome.

[v-zhengdxu-msft]

Hi @TanzilHasan 

Please try this:

Here's the sample data:

First of all, Use the headers as first row:

Then select all column and click the Transpose in the Transform pane:

Next add a custom column:

if [Column1]=[Column2] then [Column1] else Text.Combine({[Column1], [Column2]}, " ")

Remove the Column1 and Column2, select the Custom column and click Pivot Column:

The result is as follow:

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[TanzilHasan]

Thanks for providing step by step instruction, this method also works. 

[ryan_mayu]

could you pls provide more sample data? How many rows that you need to merge? The results of other rows are the same?

[lbendlin]

Having columns like this is more of an Excel thing, and not something you want to do in Power BI. You will want to unpivot your source data, and likely split it into two sources, one for the quarter attributes (AA, FG, HL) and the other for the numeric data.