Forum Discussion

TChagwedera's avatar
TChagwedera
Regular Visitor
3 years ago
Solved

Merging 2 rows

 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 me...
Need Help
Tutorial Requests
  • Greg_Deckler's avatar
    Greg_Deckler
    3 years ago

    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

     

  • TChagwedera's avatar
    TChagwedera
    Regular Visitor
    3 years ago

    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's avatar
      Greg_Deckler
      Community Champion
      3 years ago

      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's avatar
        TChagwedera
        Regular Visitor
        3 years ago

        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