Display Repeated Dates in a Row

[HMJSomerset]

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 solution]

Hi, @HMJSomerset ,

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:

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

[HotChilli]

I suppose the easiest thing to display this (and admittedly this is not the requested format) is to stick both asset and date in the rows of a matrix.  You can drill down to see all dates. Simple.

If you want to go the columns route, add a DAX column to cumulatively count the inspection date per each asset.   So a count with a filter to return the same asset and <= inspection date.  Plenty of examples around. Have a go and I will help if you get stuck.  Then put this new column in the columns section of a matrix, asset in rows and drag inspection date to values.