I have data in columns for 4 variables and each row has different item - need help in sorting

[Saqibmughal00]

Hi,

My data is as per above picture, I want to have the weekly trend based on work pack for overall earned meters, overall earned hours, actual spent during the week and PF. 

the issue is my workpacks/ modules are in column and other data is merged with date in same column. How I can have date column unpivoted with all varialbles accounted. (when I promote date as header it goes to *14/04/2024| 14/04/2024_1| ....so on.

thanks for your kind help

[MarkLaf · Accepted solution]

To try to clarify your requirements, it sounds like you want to take data in format like this:

Data

Column7	Column8	Column9	Column10	Column11	Column12
WORKPACK	Module	2/7/2024	2/7/2024	2/14/2024	2/14/2024
null	null	24	70	33	123
null	null	For the week - Metric1	For the week - Metric2	For the week - Metric1	For the week - Metric2
100-A	1	5	5	2	10
100-A	2	5	10	3	11
100-A	3	5	15	4	12
100-B	1	3	20	10	30
100-B	2	3	5	8	50
100-B	3	3	15	6	10

And get an output like this?

Output

WORKPACK	Module	Week	For the week - Metric1	For the week - Metric2
100-A	1	2/7/2024	5	5
100-A	2	2/7/2024	5	10
100-A	3	2/7/2024	5	15
100-A	1	2/14/2024	2	10
100-A	2	2/14/2024	3	11
100-A	3	2/14/2024	4	12
100-B	1	2/7/2024	3	20
100-B	2	2/7/2024	3	5
100-B	3	2/7/2024	3	15
100-B	1	2/14/2024	10	30
100-B	2	2/14/2024	8	50
100-B	3	2/14/2024	6	10

Here is some M for making such a transformation, similar to approach others have already outlined.

let
    Source = Data,
    NewColNames = List.Transform(
        Table.ToColumns(Table.FirstN(Source, 3)), each Text.Combine(List.RemoveRange(_, 1, 1), "_")
    ),
    RenameCols = Table.RenameColumns(Table.Skip(Source, 3), List.Zip({Table.ColumnNames(Source), NewColNames})),
    UnpivotCols = Table.UnpivotOtherColumns(RenameCols, {"WORKPACK", "Module"}, "Date_Metric", "Value"),
    FixUnpivots = Table.TransformColumns(
        UnpivotCols,
        {
            {
                "Date_Metric",
                each
                    [
                        split = List.Buffer(Text.Split(_, "_")),
                        wk = Date.From(split{0}),
                        metric = split{1},
                        output = [Week = wk, Metric = metric]
                    ][output],
                type [Week = date, Metric = text]
            },
            {"Value", Number.From, type number}
        }
    ),
    ExpandFields = Table.ExpandRecordColumn(FixUnpivots, "Date_Metric", {"Week", "Metric"}, {"Week", "Metric"}),
    RePivotMetrics = Table.Pivot(ExpandFields, List.Distinct(ExpandFields[Metric]), "Metric", "Value", List.Sum)
in
    RePivotMetrics

[burakkaragoz]

Hi @Saqibmughal00 ,

Your data is a mess because you've got multiple metrics per date, so when Power Query tries to make headers, it gets confused and adds those _1, _2 suffixes.

Quick fix:

Before promoting headers, combine the date with metric name:

1. Keep the first 3 rows for now
2. In row 3 (your metrics row), add text to make unique headers like:
   • "14/04/2024_EarnedMeters"
   • "14/04/2024_EarnedHours"
   • "14/04/2024_ActualSpent"
   • "14/04/2024_PF"

Then the normal process:

1. Delete first 2 rows
2. Promote row to headers
3. Unpivot everything except WorkPack and Module
4. Split the unpivoted column on "_" to separate date and metric
5. Pivot the metrics back into columns

Alternative - brute force method: Just accept the messy headers (14/04/2024, 14/04/2024_1, etc.) and manually rename them in a pattern you understand, then unpivot. Sometimes the simple approach works better than fighting with the data structure.

Your data format is pretty hostile to Power Query - whoever designed it clearly wasn't thinking about data analysis!

What do the actual headers look like when you promote that first row? Screenshot that and I can give you the exact steps.

---

If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.
This response was assisted by AI for translation and formatting purposes.

[v-sshirivolu]

Hi @Saqibmughal00 ,

You can reshape your data for weekly trend analysis by following these steps in Power Query Editor:

Remove the first 2 rows:

These rows contain headers such as "null" and variable names like "For the week - Overall Earned M2".

Go to Home - Remove Rows - Remove Top Rows and enter 2.

Promote Headers:

Select Home - Use First Row as Headers.

Identify the columns:

The first two columns should now be WORKPACK and Module.

The other columns are combinations of dates and variable names, such as: 2/7/2024, 2/7/2024_1, 2/7/2024_2, etc.

Unpivot the date columns:

Select all columns except WORKPACK and Module.

Right-click and choose Unpivot Columns.

This will result in columns: WORKPACK, Module, Attribute, and Value.

Split the Attribute column:

In the Attribute column, go to Transform - Split Column - By Delimiter and choose underscore (_).

Name the new columns Date and MetricIndex (e.g., 0 = M2, 1 = HRS, etc.).

Map Index to Variable Names:

Add a custom column to label the metrics.
= if [MetricIndex] = "0" then "Overall Earned M2"
else if [MetricIndex] = "1" then "Overall Earned HRS"
else if [MetricIndex] = "2" then "Actual Spent during the week"
else if [MetricIndex] = "3" then "PF"
else null

After this, remove the MetricIndex column.

Change data types:

Set Date as Date Type and Value as Decimal Number.

Thank you.

[Saqibmughal00]

Hi , This seemed very relevant to what I was looking for. 

some errors appeared like I cant delete 1st 2 rows as 1st row has the date

sencondly when I promote the row as header it doesnt goes _1, _2 for all dates but for 1st 3 only and for others it goes like continuous counting for Example 02/07/2024,02/07/2024_1,02/07/2024_2,02/07/2024_3,02/07/2024_4, 03/07/2024, 03/07/2024_4, 03/07/2024_5, 03/07/2024_6.....................

not sure what settings are not correct or maybe I am doing it wrong.

[v-sshirivolu]

Hi @Saqibmughal00 ,

Thanks for the update. Here’s a breakdown:

1. First Row Has Dates (not nulls)
If your first row already lists the actual dates, there’s no need to remove any rows. You can skip the “Remove Top 2 Rows” step and go straight to Promote Headers.

2. Header Naming After Promotion (_1, _2...)
Power Query adds suffixes like _1, _2, etc. to duplicate column names. Seeing higher numbers like _4, _5 for newer dates suggests that some dates have more than four metrics, or the headers weren’t merged evenly.

Suggested Fix:

After promoting headers, your columns may look like:

02/07/2024, 02/07/2024_1, 02/07/2024_2, 02/07/2024_3

03/07/2024, 03/07/2024_4, 03/07/2024_5, etc.

This happens when the same date appears multiple times.

Unpivot all columns except WORKPACK and Module, then use the Attribute column to extract the date and set the metric type using:

Date = Text.BeforeDelimiter([Attribute], "_")
MetricIndex = try Text.AfterDelimiter([Attribute], "_") otherwise "0"

Metric =
if [MetricIndex] = "0" then "Overall Earned M2"
else if [MetricIndex] = "1" then "Overall Earned HRS"
else if [MetricIndex] = "2" then "Actual Spent during the week"
else if [MetricIndex] = "3" then "PF"
else null

If this helps, you can mark it as helpful for others as well.

[v-sshirivolu]

Hi @Saqibmughal00 ,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query please mark this thread as helpful reply. 

[v-sshirivolu]

Hi @Saqibmughal00 ,

I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We’re always here to support you

[v-sshirivolu]

Hi @Saqibmughal00 ,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions

[MFelix]

Hi @Saqibmughal00 ,

I assume that the values you want has your headers are the values on row 3 I would do the following steps:

• Remove first 2 rows
• Use first row has header
• Rename the firsrt two columns  to WORKPACK to Module
• Select the column you want to keep and unpivot others