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.

     

lbendlin's avatar
lbendlin
Super User
1 year ago

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.