PowerQuery transformation

[powerbricco]

Dear All,

I have this kind of table in powerquery:

Original Table

Source.Name	ART	15/01/2025	14/01/2025	13/01/2025	12/01/2025	11/01/2025	10/01/2025	09/01/2025	08/01/2025	07/01/2025	06/01/2025	05/01/2025	04/01/2025	03/01/2025	02/01/2025	01/01/2025
1	SUM	316	491	800	740	758	327	345	764	871	550	589	453	155	504	465
1	pear	280	25	500	284	454	142	91	447	461	296	473	86	42	201	292
1	banana	36	466	300	456	304	185	254	317	410	254	116	367	113	303	173
Source.Name	ART	16/01/2025	15/01/2025	14/01/2025	13/01/2025	12/01/2025	11/01/2025	10/01/2025	09/01/2025	08/01/2025	07/01/2025	06/01/2025	05/01/2025	04/01/2025	03/01/2025	02/01/2025
2	SUM	615	316	491	800	740	758	327	345	764	871	550	589	453	155	504
2	pear	414	280	25	500	284	454	142	91	447	461	296	473	86	42	201
2	banana	201	36	466	300	456	304	185	254	317	410	254	116	367	113	303
Source.Name	ART	17/01/2025	16/01/2025	15/01/2025	14/01/2025	13/01/2025	12/01/2025	11/01/2025	10/01/2025	09/01/2025	08/01/2025	07/01/2025	06/01/2025	05/01/2025	04/01/2025	03/01/2025
3	SUM	540	615	316	491	800	740	758	327	345	764	871	550	589	453	155
3	pear	350	414	280	25	500	284	454	142	91	447	461	296	473	86	42
3	banana	190	201	36	466	300	456	304	185	254	317	410	254	116	367	113
Source.Name	ART	18/01/2025	17/01/2025	16/01/2025	15/01/2025	14/01/2025	13/01/2025	12/01/2025	11/01/2025	10/01/2025	09/01/2025	08/01/2025	07/01/2025	06/01/2025	05/01/2025	04/01/2025
4	SUM	490	540	615	316	491	800	740	758	327	345	764	871	550	589	453
4	pear	256	350	414	280	25	500	284	454	142	91	447	461	296	473	86
4	banana	234	190	201	36	466	300	456	304	185	254	317	410	254	116	367
Source.Name	ART	19/01/2025	18/01/2025	17/01/2025	16/01/2025	15/01/2025	14/01/2025	13/01/2025	12/01/2025	11/01/2025	10/01/2025	09/01/2025	08/01/2025	07/01/2025	06/01/2025	05/01/2025
5	SUM	820	490	540	615	316	491	800	740	758	327	345	764	871	550	589
5	pear	437	256	350	414	280	25	500	284	454	142	91	447	461	296	473
5	banana	383	234	190	201	36	466	300	456	304	185	254	317	410	254	116
Source.Name	ART	20/01/2025	19/01/2025	18/01/2025	17/01/2025	16/01/2025	15/01/2025	14/01/2025	13/01/2025	12/01/2025	11/01/2025	10/01/2025	09/01/2025	08/01/2025	07/01/2025	06/01/2025
6	SUM	424	820	490	540	615	316	491	800	740	758	327	345	764	871	550
6	pear	137	437	256	350	414	280	25	500	284	454	142	91	447	461	296
6	banana	287	383	234	190	201	36	466	300	456	304	185	254	317	410	254

I need to transform the above table in the following format:

Desired Table

ART	Date	Value
SUM	01/01/2025	465
SUM	02/01/2025	504
SUM	03/01/2025	155
SUM	04/01/2025	453
SUM	05/01/2025	589
SUM	06/01/2025	550
SUM	07/01/2025	871
SUM	08/01/2025	764
SUM	09/01/2025	345
SUM	10/01/2025	327
SUM	11/01/2025	758
SUM	12/01/2025	740
SUM	13/01/2025	800
SUM	14/01/2025	491
SUM	15/01/2025	316
SUM	16/01/2025	615
SUM	17/01/2025	540
SUM	18/01/2025	490
SUM	19/01/2025	820
SUM	20/01/2025	424

Do you know what actions I need to perform to go from Original Table to Desired Table?

Thank you a lot

[burakkaragoz · Accepted solution]

Hi @powerbricco ,

The answer you received is spot on and covers both the step-by-step method and a ready-to-use M code solution for your Power Query transformation.

To add a few clarifications:

• If your headers are not recognized, use “Use First Row as Headers” in Power Query. This is important for the subsequent steps to work correctly.
• When you unpivot the date columns, you’ll convert your wide table into a long format, which is exactly what you need for the “Desired Table”.
• Filtering for only SUM rows is crucial, since you only want those in your final output.
• The provided M code is a great shortcut if you’re comfortable with the Advanced Editor. Just replace "Your Previous Step" with the actual previous step name in your query.

Here’s a quick summary of the M code approach:

m

let
    Source = #"Your Previous Step", // Replace with your actual step name
    #"Removed Columns" = Table.RemoveColumns(Source,{"Source.Name"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"ART"}, "Date", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Other Columns", each ([ART] = "SUM")),
    #"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows", {{"Date", type date}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Date", Order.Ascending}})
in
    #"Sorted Rows"

With this, you’ll get exactly the output format you showed as “Desired Table”.

If you have trouble with any of the steps or need help adapting the M code to your actual table name, just let me know!

Good luck with your Power Query transformation!
translation and formatting supported by AI

[Elena_Kalina]

Hi @powerbricco 

Sorry, please, I did not immediately pay attention to your specific table, since in each subsequent line with the dates there was a shift for one day, while in your final version you would like to see only the first mention.

In accordance with this, I edited all the steps that you must perform:

// Step 1: Use first row as headers
= Table.PromoteHeaders(#"PreviousStep", [PromoteAllScalars=true])

// Step 2: Remove Source.Name column
= Table.RemoveColumns(#"Promoted Headers",{"Source.Name"})

// Step 3: Filter out banana and pear from ART column
= Table.SelectRows(#"Removed Columns", each ([ART] <> "banana" and [ART] <> "pear"))

// Step 4: Unpivot other columns (keeping ART as identifier)
= Table.UnpivotOtherColumns(#"Filtered Rows", {"ART"}, "Attribute", "Value")

// Step 5: Add index column starting from 1
= Table.AddIndexColumn(#"Unpivoted Columns", "Index", 1, 1, Int64.Type)

// Step 6: Filter to keep only needed rows (≤15 and specific indexes)
= Table.SelectRows(
    #"Added Index",
    each Number.From([Index]) <= 15 or 
    List.Contains({31, 61, 91, 121, 151}, Number.From([Index]))
)

// Step 7: Replace dates for specific indexes
= Table.ReplaceValue(
    #"Filtered Index Rows",
    each [Date],
    each if [Index] = 31 then #date(2025, 1, 16)
         else if [Index] = 61 then #date(2025, 1, 17)
         else if [Index] = 91 then #date(2025, 1, 18)
         else if [Index] = 121 then #date(2025, 1, 19)
         else if [Index] = 151 then #date(2025, 1, 20)
         else [Date],
    Replacer.ReplaceValue,
    {"Date"}
)

// Step 8: Rename 'Attitude' column to 'Date'
= Table.RenameColumns(#"PreviousStep", {{"Attitude", "Date"}})

// Step 9: Convert text dates to proper date format
= Table.TransformColumns(
    #"Renamed Columns", 
    {
        {"Date", each 
            try Date.FromText(_, "en-GB")    // Try day/month/year format first
            otherwise 
            try Date.FromText(_, "en-US")    // Then try month/day/year
            otherwise null,                   // Return null if conversion fails
            type date}
    }
)

// Step 10: Correct specific dates based on Index
= Table.ReplaceValue(
    #"Converted Date Format",
    each [Date],
    each if [Index] = 31 then #date(2025, 1, 16)
         else if [Index] = 61 then #date(2025, 1, 17)
         else if [Index] = 91 then #date(2025, 1, 18)
         else if [Index] = 121 then #date(2025, 1, 19)
         else if [Index] = 151 then #date(2025, 1, 20)
         else [Date],
    Replacer.ReplaceValue,
    {"Date"}
)

// Step 11: Sort by Date column
= Table.Sort(#"Corrected Specific Dates", {{"Date", Order.Ascending}})

// Step 12: Remove Index column
= Table.RemoveColumns(#"Sorted Rows", {"Index"})

Here is a screenshot with my result if you suddenly do not understand something, write to explain

[speedramps]

Hmmmh  ... thanks or accepting my solution but I didn't fix it. 
I meerly spotted the other solutions from @burakkaragoz  and @Elena_Kalina may not work.

[powerbricco]

https://community.fabric.microsoft.com/t5/Desktop/PQ-Transformation/m-p/4726916#M1410892

[powerbricco]

Yeah thanks a lot... I opened a new thread because it may be simpler than this, though i can'f find a solution. I think solutions of others are AI generated 😞

[speedramps · Accepted solution]

@burakkaragoz  and @Elena_Kalina 
The solution you provided wont work because each source has different date columns.
If you make the top row a header or unpivot the whole table as you have kindly suggested then it produces the wrong answer.
Try it and share a PBIX if you think it works. Thanks

[RicoZhou]

Hi @powerbricco ,

You can refer to steps as below.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("1ZY7bsMwDIbv4jloSIqU5LEHaIemnYoMaZGxDwTo/SvSkczFmzQYBmhRovyT5gfZ7+8TTofp9PZUbMBYLM86kwGKTWxWsq5SUsuiM5E1JmmkiMZInnWvhGJRNEZAYzjKdD4sKr/Xy63cKOsGWmJsmC1S1CJTsZYCc7IH6JhmSy3p47MNNYxgWaMm8XH5LpfmaUFRbTARlmVsIlksA7aiTQWhzaC9hhCTjYPtsrKKuuqcfv5un9eH58vXtcw+vrzqWjwCHgmsKhTvsHeCd8g76B1wDszeyd5J3vEZgM8AfAbgM4A1Ay2MGgkRZQgPVeVOAiN356FKNBIWSLrzsEmCb8oesdDCQiNBrOP9eagqdxKCBfXloUo0EnCGETxskuCbskcstDBuJLC9vf48VJX6dVj60JeHqrEeCoFHALGJgu/KHrnQwqShkAmGAFFV6uchpAFAVJH1VyGHEURssUC+LXsEQwuL67FAPISIqnJnAY2F3kRUkfVcyGkEEefzPw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Source.Name = _t, ART = _t, #"15/01/2025" = _t, #"14/01/2025" = _t, #"13/01/2025" = _t, #"12/01/2025" = _t, #"11/01/2025" = _t, #"10/01/2025" = _t, #"09/01/2025" = _t, #"08/01/2025" = _t, #"07/01/2025" = _t, #"06/01/2025" = _t, #"05/01/2025" = _t, #"04/01/2025" = _t, #"03/01/2025" = _t, #"02/01/2025" = _t, #"01/01/2025" = _t]),
    #"Demoted Headers" = Table.DemoteHeaders(Source),
    #"Changed Type" = Table.TransformColumnTypes(#"Demoted Headers",{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}, {"Column13", type text}, {"Column14", type text}, {"Column15", type text}, {"Column16", type text}, {"Column17", type text}}),
    #"Added Conditional Column1" = Table.AddColumn(#"Changed Type", "Source.Name", each if [Column1] = "Source.Name" then null else [Column1]),
    #"Filled Up" = Table.FillUp(#"Added Conditional Column1",{"Source.Name"}),
    #"Removed Columns" = Table.RemoveColumns(#"Filled Up",{"Column1"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"Column2","Source.Name"}, "Attribute", "Value"),
    #"Added Conditional Column" = Table.AddColumn(#"Unpivoted Other Columns", "Custom1", each if Text.Contains([Value], "/") then [Value] else null),
    #"Added Custom" = Table.AddColumn(#"Added Conditional Column", "Custom", each let _SN = [Source.Name], _Attribute = [Attribute] in Table.SelectRows(#"Added Conditional Column",each [Source.Name] = _SN and [Attribute] = _Attribute and [Custom1] = null)[Value]),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Custom", each ([Custom1] <> null)),
    #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows",{"Column2", "Source.Name", "Attribute", "Value"}),
    #"Added Custom1" = Table.AddColumn(#"Removed Columns1", "ART", each "SUM"),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Custom1",{"ART", "Custom1", "Custom"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Reordered Columns", "Custom1", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), {"Custom1.1", "Custom1.2", "Custom1.3"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Custom1.1", Int64.Type}, {"Custom1.2", Int64.Type}, {"Custom1.3", Int64.Type}}),
    #"Reordered Columns1" = Table.ReorderColumns(#"Changed Type1",{"ART", "Custom1.2", "Custom1.1", "Custom1.3", "Custom"}),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Reordered Columns1", {{"Custom1.2", type text}, {"Custom1.1", type text}, {"Custom1.3", type text}}, "zh-CN"),{"Custom1.2", "Custom1.1", "Custom1.3"},Combiner.CombineTextByDelimiter("/", QuoteStyle.None),"Date"),
    #"Renamed Columns" = Table.RenameColumns(#"Merged Columns",{{"Custom", "Value"}}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}, {"Value", Int64.Type}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type2",{{"Date", Order.Ascending}})
in
    #"Sorted Rows"

Original:

Result is as below.

Best Regards.

Rico Zhou

[burakkaragoz · Accepted solution]

Hi @powerbricco ,

The answer you received is spot on and covers both the step-by-step method and a ready-to-use M code solution for your Power Query transformation.

To add a few clarifications:

• If your headers are not recognized, use “Use First Row as Headers” in Power Query. This is important for the subsequent steps to work correctly.
• When you unpivot the date columns, you’ll convert your wide table into a long format, which is exactly what you need for the “Desired Table”.
• Filtering for only SUM rows is crucial, since you only want those in your final output.
• The provided M code is a great shortcut if you’re comfortable with the Advanced Editor. Just replace "Your Previous Step" with the actual previous step name in your query.

Here’s a quick summary of the M code approach:

m

let
    Source = #"Your Previous Step", // Replace with your actual step name
    #"Removed Columns" = Table.RemoveColumns(Source,{"Source.Name"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"ART"}, "Date", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Other Columns", each ([ART] = "SUM")),
    #"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows", {{"Date", type date}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Date", Order.Ascending}})
in
    #"Sorted Rows"

With this, you’ll get exactly the output format you showed as “Desired Table”.

If you have trouble with any of the steps or need help adapting the M code to your actual table name, just let me know!

Good luck with your Power Query transformation!
translation and formatting supported by AI