Pivot columns

[jasemilly]

Hi I have a sql source returning data like

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

[AlB · Accepted solution]

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 

[AlB]

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 

[jasemilly]

Hi AIB

is this ok?

OrganisationId                                              ExpiryDate                  Description                 ValidDocument

000c02ea-e57f-437c-a0c7-00295da00624	08/07/2019 00:00:00	Operators Licence	0
000c02ea-e57f-437c-a0c7-00295da00624	26/03/2020 00:00:00	Insurance Certificate	0
000c02ea-e57f-437c-a0c7-00295da00624	03/05/2019 00:00:00	Liability Insurance	0