Pivot columns

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