Help Merging 2 rows into one in Power Query PBI

[miguelsus2000]

Hi, i've tried many different ways, but i'm not able to merge every two rows into a single row for this table shown below.  I tried duplicating the colum and shifting it up one row, but that didn't work either.  Any help will be appreciated.  Thank you.

[AbhiSSRS · Accepted solution]

On Power Query we can do so by Grouping, making a copy and merging. This is inefficient though, maybe until someone suggests an efficient Power Query technique ( Gil Raviv on datachant.com)

Table 1 :

let
Source = Excel.Workbook(File.Contents("C:\Documents\Book9.xlsx"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(Sheet1_Sheet,{{"Column1", Int64.Type}, {"Column2", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Number.RoundUp([Column1]/2)),
#"Grouped Rows" = Table.Group(#"Added Custom", {"Custom"}, {{"Count", each List.Max([Column2]), type nullable text}})
in
#"Grouped Rows"

Now copy this table 

Table 2 :

let
Source = Excel.Workbook(File.Contents("C:\Documents\Book9.xlsx"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(Sheet1_Sheet,{{"Column1", Int64.Type}, {"Column2", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Number.RoundUp([Column1]/2)),
#"Grouped Rows" = Table.Group(#"Added Custom", {"Custom"}, {{"Count", each List.Min([Column2]), type nullable text}}),
#"Merged Queries" = Table.NestedJoin(#"Grouped Rows", {"Custom"}, Sheet1, {"Custom"}, "Sheet1", JoinKind.LeftOuter),
#"Expanded Sheet1" = Table.ExpandTableColumn(#"Merged Queries", "Sheet1", {"Custom", "Count"}, {"Sheet1.Custom", "Sheet1.Count"}),
#"Inserted Merged Column" = Table.AddColumn(#"Expanded Sheet1", "Merged", each Text.Combine({[Count], [Sheet1.Count]}, ""), type text)
in
#"Inserted Merged Column"

[v-kelly-msft · Accepted solution]

Hi @miguelsus2000 ,

Using below M codes to realize it:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Tc8rDoBAFEPRvTw9Ysp8AM0WcGQkjo+BhOWDeCV1zVG9y2LTud37AQs2r88Fa4HWuXViyS2JZbcsVtyKWHWrYr1bLza4DWKj2yiGyNNR9U/RFjAGWgPmQHvAIGgRmARtAqOgVWAWtAsM+0ZrLw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Column " = _t, Text = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column ", type text}, {"Text", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each Number.RoundUp([Index]/2,0)),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"Custom"}, {{"Merged column", each _, type table [#"Column "=nullable text, Text=nullable text, Index=number, Custom=number]}}),
    #"Added Custom1" = Table.AddColumn(#"Grouped Rows", "Custom.1", each Table.Column([Merged column],"Text")),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom1", {"Custom.1", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Removed Columns" = Table.RemoveColumns(#"Extracted Values",{"Merged column"})
in
    #"Removed Columns"

And you will see:

  

⬇

You could also realize it using dax expression,for details,pls check my .pbix file attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[AbhiSSRS]

If permitted to do through Dax and Power Query both below can be done :

in Power Query add a custom column :

Now in DAX add a measure to concatenate :

Please mark as solution if this looks ok!

Thanks!
Abhinav

[AbhiSSRS · Accepted solution]

On Power Query we can do so by Grouping, making a copy and merging. This is inefficient though, maybe until someone suggests an efficient Power Query technique ( Gil Raviv on datachant.com)

Table 1 :

let
Source = Excel.Workbook(File.Contents("C:\Documents\Book9.xlsx"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(Sheet1_Sheet,{{"Column1", Int64.Type}, {"Column2", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Number.RoundUp([Column1]/2)),
#"Grouped Rows" = Table.Group(#"Added Custom", {"Custom"}, {{"Count", each List.Max([Column2]), type nullable text}})
in
#"Grouped Rows"

Now copy this table 

Table 2 :

let
Source = Excel.Workbook(File.Contents("C:\Documents\Book9.xlsx"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(Sheet1_Sheet,{{"Column1", Int64.Type}, {"Column2", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Number.RoundUp([Column1]/2)),
#"Grouped Rows" = Table.Group(#"Added Custom", {"Custom"}, {{"Count", each List.Min([Column2]), type nullable text}}),
#"Merged Queries" = Table.NestedJoin(#"Grouped Rows", {"Custom"}, Sheet1, {"Custom"}, "Sheet1", JoinKind.LeftOuter),
#"Expanded Sheet1" = Table.ExpandTableColumn(#"Merged Queries", "Sheet1", {"Custom", "Count"}, {"Sheet1.Custom", "Sheet1.Count"}),
#"Inserted Merged Column" = Table.AddColumn(#"Expanded Sheet1", "Merged", each Text.Combine({[Count], [Sheet1.Count]}, ""), type text)
in
#"Inserted Merged Column"

[amitchandak]

@miguelsus2000 ,

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

One of the way is aggregation

https://radacad.com/power-bi-aggregation-step-1-create-the-aggregated-table

[miguelsus2000]

Hi Amit, thank you for the response.  I have a feeling it's a lot simpler than it looks, but just not sure the best way.  This data is blob data collected from an azure blob.  The files are opened, then have rows as shown.  They have a header, numeric data row, then non-numeric row, then another numeric data row, then non-numeric row and so on.  All I need is to combine the numeric row with the non-numeric row into a single row.

I tried duplicating the column and shifting one column up, but it's easy in Excel, not in PowerBI query; that would solve the problem as well.

Hope this helps explain the issue.

Thank you.
M