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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
HMJSomerset
Helper II
Helper II

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, @HMJSomerset ,

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, @HMJSomerset ,

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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