Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Nou_admin1
Frequent Visitor

How to Combine or Merge Two Rows into One Single Row

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.

 

Nou_admin1_0-1668496187424.png

 

 

Please suggest a simplest and quick solution.

 

Thanks in Advance.

 

1 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"

View solution in original post

6 REPLIES 6
BIHelper
Frequent Visitor

@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
Frequent Visitor

@AntrikshSharma Thanks for your detailed reply. Is there any other simple 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"

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

@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
Community Champion
Community Champion

@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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors