Forum Discussion

TanzilHasan's avatar
TanzilHasan
Frequent Visitor
1 year ago
Solved

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 i...
Need Help
Show and Tell
  • Ashish_Mathur's avatar
    Ashish_Mathur
    1 year ago

    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.

     

Anonymous's avatar
Anonymous
Not applicable
1 year ago

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's avatar
    TanzilHasan
    Frequent Visitor
    1 year ago

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