Forum Discussion
Concat rows in PowerQuery
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 |
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 Custom1Hope this helps.
7 Replies
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.Thanks for providing step by step instruction, this method also works.
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 Custom1Hope this helps.
Thank you so much, it works.
You are welcome.
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.
could you pls provide more sample data? How many rows that you need to merge? The results of other rows are the same?
