Merging 2 rows

[TChagwedera]

 Good morning 

I'm new to Power BI and working on some project to do reports and dashboards. I'm stuck with two rows that I wish to merge so that it becomes my header. How do I do that? I want to merge first row and the Second one

[Greg_Deckler]

@TChagwedera So, what is the intended result that you want?

[TChagwedera]

Hello Greg

thank you for your response.. so I wish to have the details in row 2 to be combined with the information in row 1. For example I want the years to be combined with that Total number served heading in row 2

[Greg_Deckler]

@TChagwedera So just for the years columns or for all columns, in other words do you want:

Connections: End Date & Time... Volunteer Opportunity: Man..., Occurence: Location: Location Name, April 2022 Total Number of Hours Served, May 2022 Total Number of Hours Served

or:

Connections: End Date & Time..., null, April 2022 Total Number of Hours Served, May 2022 Total Number of Hours Served

or

Connections: End Date & Time..., Occurence: Location: Location Name, April 2022 Total Number of Hours Served, May 2022 Total Number of Hours Served

or

Connections: End Date & Time..., Occurence: Location: Location Name, Total Number of Hours Served April 2022, Total Number of Hours Served May 2022

or possibly 1 of the millions of potential permutations?

[TChagwedera]

I would like to go with option 1 if it's possible or if you have any tips on how I can go about it please help

[Greg_Deckler · Accepted solution]

@TChagwedera 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hY49C4MwEIb/ypHZwVo/iltpCx2qDpUu1iGNVxA0kZgU/PfNRaFjISF37/NwuaZhJyUlCtMrOedwkR2cuUF42jCMUqj7EVnA3DlOuh8gCqPINQVf1rINGvZQg5UGUUM1TUobK3uz5FBw6cxKCKtRCszhpgSnb34VlNyPr5XhA5R2fLkh6g1XZfUMd9Qf7P5h2mDnpL279K470ZKx74hQQjzZvNgnxNPNS3xCPNu81CfED5uXsbb9Ag==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}}),
    #"Transposed Table" = Table.Transpose(#"Changed Type"),
    #"Merged Columns" = Table.CombineColumns(#"Transposed Table",{"Column1", "Column2"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"),
    #"Transposed Table1" = Table.Transpose(#"Merged Columns"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table1", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Connections: End Date & Time Volunteer Opportunity: Man", Int64.Type}, {" Occurence: Location: Location Name", Int64.Type}, {"April 2022 Total Number of Hours Served", Int64.Type}, {"May 2022 Total Number of Hours Served", Int64.Type}})
in
    #"Changed Type1"

In English:

1. Transpose your table
2. Select the first two columns and Merge
3. Transpose your table