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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
jasemilly
Helper I
Helper I

Pivot columns

Hi I have a sql source returning data like

jasemilly_0-1607615450489.png

 

how do I pivot it so it shows

 

Org id  |   Expiry Date Operators Licence  | Operators Licence | Expiry Date Insurance Certificate | Insurance Certificate     

xxx       |                08/07/2019                   |             0                |                26/03/2020                   |                 0

 

And include the libiality Insurance with an expiry date.

I can do a normal pivot on the description but no idea with the expiry date

 

Thanks

Jason

1 ACCEPTED SOLUTION
AlB
Super User
Super User

Hi @jasemilly 

Place the following M code in a blank query to see the steps.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lc7BCsIwEATQXwk5N2RMm8Z69SQU/IDSw7puISBV0njw700RRDwp7GkY3uwwaAAMJ2TEh8k0dWBD4GAA1/kzAa1rdKWxtQjWYdMp7LBeCY83SZSvaVF9ZJlZ1qIeq59V11rURXX4UA/zck9UNLWXlOMUmfLfcmHhv//tI53iJeaHem+83PEJ", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [OrganisationId = _t, ExpiryDate = _t, Description = _t, ValidDocument = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"OrganisationId", type text}, {"ExpiryDate", type datetime}, {"Description", type text}, {"ValidDocument", Int64.Type}}),


    #"Added Custom" = Table.AddColumn(#"Changed Type", "Description copy", each "Expiry date " & [Description]),
    #"Pivoted Column" = Table.Pivot(#"Added Custom", List.Distinct(#"Added Custom"[Description]), "Description", "ValidDocument", List.Sum),
    #"Pivoted Column1" = Table.Pivot(#"Pivoted Column", List.Distinct(#"Pivoted Column"[#"Description copy"]), "Description copy", "ExpiryDate", List.Sum),
    #"Grouped Rows" = Table.Group(#"Pivoted Column1", {"OrganisationId"}, {{"All rows", each Table.FirstN(Table.FillDown(Table.FillUp(_, Table.ColumnNames(_)),Table.ColumnNames(_)),1)}}),
    #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"OrganisationId"}),
    #"Expanded All rows" = Table.ExpandTableColumn(#"Removed Columns", "All rows", {"OrganisationId", "Operators Licence", "Insurance Certificate", "Liability Insurance", "Expiry date Liability Insurance", "Expiry date Operators Licence", "Expiry date Insurance Certificate"}, {"OrganisationId", "Operators Licence", "Insurance Certificate", "Liability Insurance", "Expiry date Liability Insurance", "Expiry date Operators Licence", "Expiry date Insurance Certificate"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded All rows",{"OrganisationId", "Expiry date Operators Licence", "Operators Licence", "Expiry date Insurance Certificate", "Insurance Certificate", "Expiry date Liability Insurance", "Liability Insurance"})
in
    #"Reordered Columns"

 

 

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

 

SU18_powerbi_badge

View solution in original post

3 REPLIES 3
AlB
Super User
Super User

Hi @jasemilly 

Place the following M code in a blank query to see the steps.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lc7BCsIwEATQXwk5N2RMm8Z69SQU/IDSw7puISBV0njw700RRDwp7GkY3uwwaAAMJ2TEh8k0dWBD4GAA1/kzAa1rdKWxtQjWYdMp7LBeCY83SZSvaVF9ZJlZ1qIeq59V11rURXX4UA/zck9UNLWXlOMUmfLfcmHhv//tI53iJeaHem+83PEJ", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [OrganisationId = _t, ExpiryDate = _t, Description = _t, ValidDocument = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"OrganisationId", type text}, {"ExpiryDate", type datetime}, {"Description", type text}, {"ValidDocument", Int64.Type}}),


    #"Added Custom" = Table.AddColumn(#"Changed Type", "Description copy", each "Expiry date " & [Description]),
    #"Pivoted Column" = Table.Pivot(#"Added Custom", List.Distinct(#"Added Custom"[Description]), "Description", "ValidDocument", List.Sum),
    #"Pivoted Column1" = Table.Pivot(#"Pivoted Column", List.Distinct(#"Pivoted Column"[#"Description copy"]), "Description copy", "ExpiryDate", List.Sum),
    #"Grouped Rows" = Table.Group(#"Pivoted Column1", {"OrganisationId"}, {{"All rows", each Table.FirstN(Table.FillDown(Table.FillUp(_, Table.ColumnNames(_)),Table.ColumnNames(_)),1)}}),
    #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"OrganisationId"}),
    #"Expanded All rows" = Table.ExpandTableColumn(#"Removed Columns", "All rows", {"OrganisationId", "Operators Licence", "Insurance Certificate", "Liability Insurance", "Expiry date Liability Insurance", "Expiry date Operators Licence", "Expiry date Insurance Certificate"}, {"OrganisationId", "Operators Licence", "Insurance Certificate", "Liability Insurance", "Expiry date Liability Insurance", "Expiry date Operators Licence", "Expiry date Insurance Certificate"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded All rows",{"OrganisationId", "Expiry date Operators Licence", "Operators Licence", "Expiry date Insurance Certificate", "Insurance Certificate", "Expiry date Liability Insurance", "Liability Insurance"})
in
    #"Reordered Columns"

 

 

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

 

SU18_powerbi_badge

AlB
Super User
Super User

Hi @jasemilly 

Please paste the contents of the screen cap (initial table) in text-tabular format (like the second) so tha it can be copied

 

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

SU18_powerbi_badge

Hi AIB

 

is this ok?

OrganisationId                                              ExpiryDate                  Description                 ValidDocument

000c02ea-e57f-437c-a0c7-00295da0062408/07/2019 00:00:00Operators Licence0
000c02ea-e57f-437c-a0c7-00295da0062426/03/2020 00:00:00Insurance Certificate0
000c02ea-e57f-437c-a0c7-00295da0062403/05/2019 00:00:00Liability Insurance0

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors