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.

[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