cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
LeroyPaul
Frequent Visitor

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

1 ACCEPTED SOLUTION
AlexisOlson
Super User
Super User

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

 

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

 

View solution in original post

2 REPLIES 2
AlexisOlson
Super User
Super User

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

 

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

 

View solution in original post

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

So much time wasted on such simple steps

You made my day ! 😁

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors