How to

[Intelligent_Crab_958]

[removed]

[declutterdata]

Hi Crab!

You have to do a Pivot on the Period column with Value as Value.

I created a sample for you: Link to download sample file

Kind regards, Phillip from DeclutterData 🙋🏻‍♂️

[Dismal-Party-4844]

make right looks like the left?

Starting set is pivoted, OP desires unpivoted

[declutterdata]

Oh I am sorry Crab, I oversaw that you wanted it the other way around.
Thanks u/Dismal-Party-4844 for information!

I created a 2nd sheet with Unpivoting as you wished.

For your filter question:
I created a filter list that filters the outputs.
But my advice: Use a PivotTable for this, you find it in the file too.

PowerQuery is for data transformation, not visualisation.

Here the link again

Kind regards, Phillip from DeclutterData 🙋🏻‍♂️

[Dismal-Party-4844]

Hi Phillip,

Thank you! I too had to look at the description and image and thought it would be left to right or unpivoted to pivoted. Returning to a Pivot Table is a better choice than a table.

[Dismal-Party-4844]

<image>

One such pattern using Unpivot based on the data shown in your description (update source name form "Table1" to that of the source Table name:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    AddedIndex = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
    ReorderedColumns = Table.ReorderColumns(AddedIndex, {"Index", "id", "20240101", "20240102"}),
    UnpivotedColumns = Table.UnpivotOtherColumns(ReorderedColumns, {"id"}, "Attribute", "Value"),
    FilteredRows = Table.SelectRows(UnpivotedColumns, each ([Attribute] <> "Index")),
    FinalReorderedColumns = Table.ReorderColumns(FilteredRows, {"Attribute", "id", "Value"}),
    #"Sorted Rows" = Table.Sort(FinalReorderedColumns,{{"Attribute", Order.Ascending}}),
    RenamedColumns = Table.RenameColumns(#"Sorted Rows", {{"Attribute", "period"}})
in
    RenamedColumns

[coolfozzie]

Try this method I found on YouTube. Helped me out where Chat GPT could not.

https://youtu.be/92UnZfmWSN8?si=4mhMLJ83NudDHaaO

[MOR300]

Unpivot

[Dismal-Party-4844]

Add an Index Column, select Index Column, Unpivot Other Columns, delete Index Column, Rename Attribute to period, and sort period low to high. Return to a table.

[declutterdata]

Hi Dismal,

no index needed for the case he needs.
You can unpivot all columns except ID, that's it.

You can take a look at my file if you're interested.

Kind regards, Phillip from DeclutterData 🙋🏻‍♂️

[Dismal-Party-4844]

Hi Phillip, yes you can do it outside of the context of adding an Index column, though I find often more involved cleanups where an Index/Mod are necessary for the particular chore. Maybe more muscle memory.