How to Combine or Merge Two Rows into One Single Row

[Nou_admin1]

Hi Friends,

Anyone Please suggest me a idea in power query how to make a single title row by combine two rows title.  Refer the below snapshot. In First two I have the Title in Excel sheet this type. Like first 3 column title contains in the Row1 and rest of the column main title are contains in Row2. So I want to make it in single row either merge or keep any one row as main title. Replace the Null Value in Row2 with Row1 value (or) Replace the Row1 Total as Row2 Value.

Please suggest a simplest and quick solution.

Thanks in Advance.

[Anonymous]

@Nou_admin1 -Hello,From where you are taking the data if it is excel then may i see your column names in the excel sheets?
so that i can understand the column names properly.

[Nou_admin1]

@AntrikshSharma Thanks for your detailed reply. Is there any other simple solution ?

[latimeria · Accepted solution]

Hi @Nou_admin1 ,

Try this:

transpose table

merge first 2 columns

transpose table

promote header

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCjFU0lEqMQIRxkqxOtFKQFYxiFsM4SaBZIDYDM4rAmJDIzi3CohNTMDcZCAzDYhN4bx0IIYoBbEygNgYhWcC54FMtVCKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]),
    #"Transposed Table" = Table.Transpose(Source),
    #"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])
in
    #"Promoted Headers"

[AntrikshSharma]

@Nou_admin1 Yes, convert your data into an Excel table and make sure column headers are already correct in the original data.

[Nou_admin1]

@AntrikshSharmaThat I did already and used. Like Replace the Value by type the Value manually for the First 3 Rows...

Anyway Thanks for your detail source and It may helpful in any other datasets.

[AntrikshSharma]

@Nou_admin1 Try this:

let
    Source = 
        Table.FromRows (
            Json.Document (
                Binary.Decompress (
                    Binary.FromText (
                        "i45Wcs7PK85Mz0tNVdJRCijKTylNLol3zk9B4gJZIfkliTk46VidaCUgB4KCHX1cgZRbkCuIcs7JL87MS1cILslPzgbJgmjdzDzdkKJEoL0lYL0uiSWJQDliqdhYAA==",
                        BinaryEncoding.Base64
                    ),
                    Compression.Deflate
                )
            ),
            let
                _t = ( ( type nullable text ) meta [ Serialized.Text = true ] )
            in
                type table [
                    #"Corporate OFFICE RAIPUR" = _t,
                    Column2 = _t,
                    Column3 = _t,
                    Column4 = _t,
                    Column5 = _t,
                    Column6 = _t,
                    Column7 = _t
                ]
        ),
    ChangedType = 
        Table.TransformColumnTypes (
            Source,
            {
                { "Corporate OFFICE RAIPUR", type text },
                { "Column2", type text },
                { "Column3", type text },
                { "Column4", type text },
                { "Column5", type text },
                { "Column6", type text },
                { "Column7", type text }
            }
        ),
    FirstPart = 
        List.RemoveItems (
            Table.ToRows ( 
                Table.Range ( ChangedType, 0, 1 ) 
            ){0},
            { "Total" }
        ),
    SecondPart = 
        List.RemoveItems ( 
            Table.ToRows ( 
                Table.Range ( ChangedType, 1, 1 ) 
            ){0}, 
            { "" } 
        ),
    NewColumnNames = FirstPart & SecondPart,
    OldColumnNames = Table.ColumnNames ( ChangedType ),
    RemovedTopRows = Table.Skip ( ChangedType, 2 ),
    Result = 
        Table.RenameColumns ( 
            RemovedTopRows, 
            List.Zip ( { OldColumnNames, NewColumnNames } ) 
        )
in
    Result