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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Anonymous
Not applicable

Display Repeated Dates in a Row

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

AssetInspection Date
Asset 1 23/6/22
Asset 223/4/2018
Asset 122/5/20
Asset 301/12/2021
Asset 113/4/2019
Asset 31/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 dateInspection dateInspection Date
Asset 123/6/2222/5/2013/4/2019
Asset 223/4/2018  
Asset 301/12/20211/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

1 ACCEPTED SOLUTION
v-yalanwu-msft
Community Support
Community Support

Hi, @Anonymous ,

You could pivot it in power query.

1.unpivot it.

vyalanwumsft_0-1656578106079.png

2.group by and add index.

= Table.Group(#"Unpivoted Columns", {"Asset"}, {{"all", each Table.AddIndexColumn(_, "Index",1,1), type table }})

vyalanwumsft_1-1656578147222.png

3.merge column.

vyalanwumsft_2-1656578171413.png

4.pivot column.

vyalanwumsft_3-1656578204884.png

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:

vyalanwumsft_4-1656578266767.png

 


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.

 

View solution in original post

2 REPLIES 2
v-yalanwu-msft
Community Support
Community Support

Hi, @Anonymous ,

You could pivot it in power query.

1.unpivot it.

vyalanwumsft_0-1656578106079.png

2.group by and add index.

= Table.Group(#"Unpivoted Columns", {"Asset"}, {{"all", each Table.AddIndexColumn(_, "Index",1,1), type table }})

vyalanwumsft_1-1656578147222.png

3.merge column.

vyalanwumsft_2-1656578171413.png

4.pivot column.

vyalanwumsft_3-1656578204884.png

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:

vyalanwumsft_4-1656578266767.png

 


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
Super User
Super User

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.

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.