Solved: Transform Table

Anonymous · ‎03-04-2022

Hello,

I'm trying to do some transformations in a table.

My original table is like this:

Year	Group	M_Checklist_01	M_Checklist_02	M_Checklist_03	M_Checklist_04	M_Checklist_05	M_Checklist_06	M_Checklist_07	M_Checklist_08	M_Checklist_09	M_Checklist_10	M_Checklist_11	M_Checklist_12
2021	M10	90%	50%	50%	90%	100%	90%	100%	90%	90%	90%	90%	10%
2021	M11	0%	50%	50%	0%	100%	0%	100%	0%	0%	0%	0%	10%
2022	M12	0%	50%	50%	0%	100%	0%	100%	0%	0%	0%	0%	10%
2022	M13	0%	50%	50%	0%	100%	0%	100%	0%	0%	0%	0%	10%

And I want to achive something like this:

DATE	GROUP	M_Checklist
01/01/2021	M10	90%
01/02/2021	M10	50%
01/03/2021	M10	50%
01/04/2021	M10	90%
01/05/2021	M10	100%
01/06/2021	M10	90%
01/07/2021	M10	100%
01/08/2021	M10	90%
01/09/2021	M10	90%
01/10/2021	M10	90%
01/11/2021	M10	90%
01/12/2021	M10	10%
01/01/2021	M11	0%
01/02/2021	M11	50%
01/03/2021	M11	50%
01/04/2021	M11	0%
01/05/2021	M11	100%
01/06/2021	M11	0%
01/07/2021	M11	100%
01/08/2021	M11	0%
01/09/2021	M11	0%
01/10/2021	M11	0%
01/11/2021	M11	0%
01/12/2021	M11	10%
01/01/2021	M12	0%
01/02/2021	M12	50%
01/03/2021	M12	50%
01/04/2021	M12	0%
01/05/2021	M12	100%
01/06/2021	M12	0%
01/07/2021	M12	100%
01/08/2021	M12	0%
01/09/2021	M12	0%
01/10/2021	M12	0%
01/11/2021	M12	0%
01/12/2021	M12	10%
01/01/2021	M13	0%
01/02/2021	M13	50%
01/03/2021	M13	50%
01/04/2021	M13	0%
01/05/2021	M13	100%
01/06/2021	M13	0%
01/07/2021	M13	100%
01/08/2021	M13	0%
01/09/2021	M13	0%
01/10/2021	M13	0%
01/11/2021	M13	0%
01/12/2021	M13	10%

-> M_Chechpoint_1 equals to the first M_Checkpoint (January) in the defined year for each group; M_Chechpoint_2 equals to the second M_Checkpoint (february) in the defined year for each group;...

Anyway on how to do that?

Best regards

AlexisOlson · ‎03-04-2022

The basic step is to unpivot all except the first two columns. Then extract the month number and combine it with the Year to define the date.

Here's a full sample query you can paste into the Advanced Editor of a new blank query to examine the steps:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMlTSUfI1NACSlgaqQNIUiYSIGBrg4mCShkAyVgfJYBCJYS6ySRhsNALJRCOwiUZUN9GYKibGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Year = _t, Group = _t, M_Checklist_01 = _t, M_Checklist_02 = _t, M_Checklist_03 = _t, M_Checklist_04 = _t, M_Checklist_05 = _t, M_Checklist_06 = _t, M_Checklist_07 = _t, M_Checklist_08 = _t, M_Checklist_09 = _t, M_Checklist_10 = _t, M_Checklist_11 = _t, M_Checklist_12 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Year", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Year", "Group"}, "Month", "M_Checklist"),
    #"Extracted Last Characters" = Table.TransformColumns(#"Unpivoted Other Columns", {{"Month", each Number.From(Text.End(_, 2)), Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Extracted Last Characters", "DATE", each #date([Year], [Month], 1), type date)
in
    #"Added Custom"

View solution in original post

AlexisOlson · ‎03-04-2022

The basic step is to unpivot all except the first two columns. Then extract the month number and combine it with the Year to define the date.

Here's a full sample query you can paste into the Advanced Editor of a new blank query to examine the steps:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMlTSUfI1NACSlgaqQNIUiYSIGBrg4mCShkAyVgfJYBCJYS6ySRhsNALJRCOwiUZUN9GYKibGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Year = _t, Group = _t, M_Checklist_01 = _t, M_Checklist_02 = _t, M_Checklist_03 = _t, M_Checklist_04 = _t, M_Checklist_05 = _t, M_Checklist_06 = _t, M_Checklist_07 = _t, M_Checklist_08 = _t, M_Checklist_09 = _t, M_Checklist_10 = _t, M_Checklist_11 = _t, M_Checklist_12 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Year", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Year", "Group"}, "Month", "M_Checklist"),
    #"Extracted Last Characters" = Table.TransformColumns(#"Unpivoted Other Columns", {{"Month", each Number.From(Text.End(_, 2)), Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Extracted Last Characters", "DATE", each #date([Year], [Month], 1), type date)
in
    #"Added Custom"