Forum Discussion
Pivot in Line
Hello,
First of all, please excuse me for my bad English
It's been a long time since I've been unable to solve my problem and because I cannot explain it well, I have not been unable to find the solution online.
I have data loaded in this format
Index | Jalon 1_Date initiale | Jalon 1_Date révisée |
| Jalon 2_Date initiale | Jalon 2_Date révisée | Jalon 3_Date initiale | Jalon 3_Date révisée |
1 | 20/02/2021 | 20/03/2021 |
| 15/02/2021 | 15/03/2021 | 11/02/2021 | 11/03/2021 |
I want to transform this database to have this format
Index | Jalon Date initiale | Jalon Date révisée |
1 | 20/02/2021 | 20/03/2021 |
1 | 15/02/2021 | 15/03/2021 |
1 | 11/02/2021 | 11/03/2021 |
I feel like I've tried everything, if someone can help me that would allow me to finally continue on my project
Thanks in advance
Paul
This is similar to a question I've answered before.
- Unpivot all the date columns
- Split on the underscore
- Re-pivot (on init_rev with Date as the values column with no aggregation)
- Rename and/or cleanup as desired.
Try pasting this into the Advanced Editor and looking at the steps in the query editor:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTLSNzIAIiMQ2xiJbaRvaIoQR7CB4oZI4lB2bCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Index = _t, #"Jalon 1_Date initiale" = _t, #"Jalon 1_Date révisée" = _t, #"Jalon 2_Date initiale" = _t, #"Jalon 2_Date révisée" = _t, #"Jalon 3_Date initiale" = _t, #"Jalon 3_Date révisée" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Index", Int64.Type}, {"Jalon 1_Date initiale", type date}, {"Jalon 1_Date révisée", type date}, {"Jalon 2_Date initiale", type date}, {"Jalon 2_Date révisée", type date}, {"Jalon 3_Date initiale", type date}, {"Jalon 3_Date révisée", type date}}), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Index"}, "ColName", "Date"), #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Columns", "ColName", Splitter.SplitTextByDelimiter("_", QuoteStyle.Csv), {"Jalon", "init_rev"}), #"Pivoted Column" = Table.Pivot(#"Split Column by Delimiter", List.Distinct(#"Split Column by Delimiter"[init_rev]), "init_rev", "Date"), #"Renamed Columns" = Table.RenameColumns(#"Pivoted Column",{{"Date initiale", "Jalon Date initiale"}, {"Date révisée", "Jalon Date révisée"}}) in #"Renamed Columns"- Unpivot all the date columns
2 Replies
This is similar to a question I've answered before.
- Unpivot all the date columns
- Split on the underscore
- Re-pivot (on init_rev with Date as the values column with no aggregation)
- Rename and/or cleanup as desired.
Try pasting this into the Advanced Editor and looking at the steps in the query editor:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTLSNzIAIiMQ2xiJbaRvaIoQR7CB4oZI4lB2bCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Index = _t, #"Jalon 1_Date initiale" = _t, #"Jalon 1_Date révisée" = _t, #"Jalon 2_Date initiale" = _t, #"Jalon 2_Date révisée" = _t, #"Jalon 3_Date initiale" = _t, #"Jalon 3_Date révisée" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Index", Int64.Type}, {"Jalon 1_Date initiale", type date}, {"Jalon 1_Date révisée", type date}, {"Jalon 2_Date initiale", type date}, {"Jalon 2_Date révisée", type date}, {"Jalon 3_Date initiale", type date}, {"Jalon 3_Date révisée", type date}}), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Index"}, "ColName", "Date"), #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Columns", "ColName", Splitter.SplitTextByDelimiter("_", QuoteStyle.Csv), {"Jalon", "init_rev"}), #"Pivoted Column" = Table.Pivot(#"Split Column by Delimiter", List.Distinct(#"Split Column by Delimiter"[init_rev]), "init_rev", "Date"), #"Renamed Columns" = Table.RenameColumns(#"Pivoted Column",{{"Date initiale", "Jalon Date initiale"}, {"Date révisée", "Jalon Date révisée"}}) in #"Renamed Columns"Thank you very much! This is exactly the result I needed
So much time wasted on such simple steps
You made my day ! 😁
- Unpivot all the date columns
