Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
LeroyPaul
Helper I
Helper I

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"

 

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors