Display Repeated Dates in a Row

Question

I have a table of inspection visits which has an Asset Column and an Inspection Date Column.

Asset	Inspection Date
Asset 1	23/6/22
Asset 2	23/4/2018
Asset 1	22/5/20
Asset 3	01/12/2021
Asset 1	13/4/2019
Asset 3	1/12/2020

I have had a request from one of the inspectors for a report that lists each asset and then in the columns each time they have been inspected.

e.g

Asset	Inspection date	Inspection date	Inspection Date
Asset 1	23/6/22	22/5/20	13/4/2019
Asset 2	23/4/2018
Asset 3	01/12/2021	1/12/2020

I know this should be possible, but I am struggling with how to do it. Help please

So far the max number of inspections for any asset is 6

v-yalanwu-msft · Accepted Answer

Hi,&nbsp;Anonymous&nbsp;,
You could pivot it in power query.
1.unpivot it.

2.group by and add index.
= Table.Group(#"Unpivoted Columns", {"Asset"}, {{"all", each Table.AddIndexColumn(_, "Index",1,1), type table }})

3.merge column.

4.pivot column.

M:
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XcxBCoAgFIThq8hbC88ZK2rZOcSlJ7D70wQJ4vbjnynF7t7bE2DRArMfTlqNgymWbs6Ec/IvJ32XT5qlCQ6KiSXHf3Mtg9HrqL4=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Asset = _t, #"Inspection Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Asset", type text}, {"Inspection Date", type text}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Asset", Order.Ascending}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Sorted Rows", {"Asset"}, "Attribute", "Value"),
    #"Grouped Rows" = Table.Group(#"Unpivoted Columns", {"Asset"}, {{"all", each Table.AddIndexColumn(_, "Index",1,1), type table }}),
    #"Expanded all" = Table.ExpandTableColumn(#"Grouped Rows", "all", {"Attribute", "Value", "Index"}, {"all.Attribute", "all.Value", "all.Index"}),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Expanded all", {{"all.Index", type text}}, "en-US"),{"all.Attribute", "all.Index"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Date"),
    #"Pivoted Column" = Table.Pivot(#"Merged Columns", List.Distinct(#"Merged Columns"[Date]), "Date", "all.Value")
in
    #"Pivoted Column"
the final show:

&nbsp;
Best Regards,Community Support Team _ Yalan WuIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
&nbsp;