Pivot in Line

[LeroyPaul]

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

[AlexisOlson · Accepted solution]

This is similar to a question I've answered before.

1. Unpivot all the date columns
   
2.  Split on the underscore
   
3. Re-pivot (on init_rev with Date as the values column with no aggregation)
   
4.  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"

[LeroyPaul]

Thank you very much! This is exactly the result I needed

So much time wasted on such simple steps

You made my day ! 😁