Merge row cells

[gauravg3]

Can someone pls advise if its possible to merge data from cells in 2 rows. In the below screenshot, I want the data from columns B-E against the name and add the date in a separate column. In the source excel, the data is against the date.

Expected outcome:

Thanks and appreciate your support.

[ronrsnfld]

You can shift the first column down one, then delete alternate rows.

Other stuff in the code is just housekeeping (renaming and reordering the columns, and setting the appropriate data types.

Paste into Advanced Editor

let

//Replace Source line with your actual data source
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hZDBCsIwDIZfpfQ8sjRtbT2KggfRy45lhx6KE8YmMt/fNhPmqUIgJPz5+EgI8jDGSZxkI7fqmyCVbtG1hGTzxoGm3BCU58libgTGcLRb0nMQ1zrCgCkIDfiD0LCzK2KI70lc6ggCXY4sEG4ICx45ek7z657Esc5QYIlRTrMUL/eFWKK3uMTxkUT37x1sgKvPV8SzSP8B", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Start Date" = _t, #"Duration A" = _t, #"Duration B" = _t, #"Duration C" = _t, #"Duration D" = _t]),
    //Add Shifted first Column
    #"Add Shifted Start Date" = Table.FromColumns(
        Table.ToColumns(Source)
         & {{null} & List.RemoveLastN(Source[Start Date])},
            Table.ColumnNames(Source) & {"Shifted Start Date"}),
    #"Removed Alternate Rows" = Table.AlternateRows(#"Add Shifted Start Date",0,1,1),

//Swap first and last column and rename
    #"Reorder and Rename Columns" = 
        [a=Table.ColumnNames(#"Removed Alternate Rows"),
         b={List.Last(a)} & List.RemoveLastN(List.Skip(a)) & {List.First(a)},
         c=Table.ReorderColumns(#"Removed Alternate Rows",b),
         d=Table.RenameColumns(c, List.Zip({{List.First(b),"Start Date"}, {List.Last(b),"Date"}})),
         e=Table.ColumnNames(d),
         f=Table.TransformColumnTypes(d,
         {{List.First(e), type text},{List.Last(e), type date}} & List.Transform(List.RemoveLastN(List.Skip(e)), each {_, type number}),"en-150")
        ][f]
    
in
    #"Reorder and Rename Columns"

Results from your data:

[AlienSx]

simply add new column

Table.AddColumn(Source, "Name", each if [Start Date] is text then [Start Date] else null)

then fill down that column and filter nulls in any of "duration" columns.

[v-mdharahman]

Hi @gauravg3,

Thanks for reaching out to the Microsoft fabric community forum.

It looks like yoiu are looking for a way to merge two rows. As @collinsg and @jaineshp have already responded to your query, kindl go through their responses and check if the issue can be resolved.

I would also take a moment to thank @collinsg and @jaineshp, for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.

If I misunderstand your needs or you still have problems on it, please feel free to let us know.  

Best Regards,
Hammad.

[collinsg]

Good Day gauravg3,

There will be more elegant ways to do this but here's an approach which works...

1. Load the table into Power Query.
2. Add an index column (starting at 0).
3. Add a conditional column called "Name" with the logic "if the index in the current row is odd populate "Name" with the value of the previous row of the "Start Date" column (which will be a name), otherwise set it as null.
4. Tidy up by
   1. Removing the alternate rows you don't need.
   2. Removing the index column.
   3. Extract the date from the "Start Date"
   4. Set the correct type for the "Name" column.
   5. Rename columns as you wish (in my example I used "Name" for the name and "Start Date" for the date.
   6. Reorder columns if you wish.

The result looks as follows

The code looks as follows

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Start Date", type any}, {"Duration A", type number}, {"Duration B", type number}, {"Duraction C", type number}, {"Duration D", type number}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
#"Added Custom" = Table.AddColumn(#"Added Index", "Name", each if Number.IsOdd([Index]) then #"Changed Type"[Start Date]{[Index]-1} else null),
#"Removed Alternate Rows" = Table.AlternateRows(#"Added Custom",0,1,1),
#"Removed Other Columns" = Table.SelectColumns(#"Removed Alternate Rows",{"Start Date", "Duration A", "Duration B", "Duraction C", "Duration D", "Name"}),
#"Extracted Date" = Table.TransformColumns(#"Removed Other Columns",{{"Start Date", DateTime.Date, type date}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Extracted Date",{{"Name", type text}}),
#"Reordered Columns" = Table.ReorderColumns(#"Changed Type1",{"Name", "Duration A", "Duration B", "Duraction C", "Duration D", "Start Date"})
in
#"Reordered Columns"

I've attached an example Excel file.

Hope this helps

[jaineshp]

Hey @gauravg3,

Looking at your screenshots, you want to transform data from having dates as row headers to having names as row headers with dates in a separate column.

Solution Steps:

1. Select your data table in Power Query Editor
2. Use "Unpivot Columns" feature:
   • Select columns B, C, D, E (Duration columns)
   • Right-click → Unpivot Columns
3. Rename the new columns:
   • "Attribute" → "Duration Type"
   • "Value" → "Duration"
4. Add the date column by duplicating the Start Date column
5. Clean up column headers and data types as needed

Alternative approach if above doesn't work:

• Use Transpose first, then Unpivot
• This will flip your rows/columns structure before unpivoting

Try the unpivot method first, it should give you the exact structure shown in your second image.

Fixed? ✓ Mark it • Share it • Help others!

Best Regards,
Jainesh Poojara | Power BI Developer