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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.