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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
vpsoini
Helper I
Helper I

Slice data including rows with common values

Hi,

 

This sounds very straight forward case, but just cannot get my head straight on this one.  

 

I have table with vehicles and their options.  Specific vehicle and it's options have the same value in order number column, but i want to slice/search data based on serial number, which only vehicle row itself has, but options don't.  So my table looks like:

 

OrderNumber | Item | Description | SerialNumber

0001 | vehicle | vehicle number one | 100001

0001 | option1 | option 1/1 | 

0001 | option2 | option 2/1 | 

0001 | option3 | option 3/1 |

0001 | option4 | option 4/1 |

0002 | vehicle | vehicle number two | 100002

0002 | option2 | option 2/2 | 

0002 | option3 | option 3/2 | 

0003 | vehicle | vehicle number three | 100003

0003 | option1 | option 1/3 | 

0003 | option3 | option 3/3 | 

0003 | option5 | option 5/3 |

 

My goal is to have a search field for serial number, after which the report would slice the corresponding vehicle into one card and icluded options into another table.  I could easily use ordernumber for that, but the search should be done on the serial number only.

 

So what is the easiest way to include those options, with the same ordernumber as vehicle, into a table, when report is sliced using the serial number?  If I use e.g. serial 100001, I would like to see

Vehicle number one (100001)
- option 1/1

- option 2/1

- option 3/1

- option 4/1

 

Thanks in advance for your help. Much appreciated 🙂

1 ACCEPTED SOLUTION

Hi @vpsoini,

I created a sample pbix file(see attachment), please check whether that is what you want.

1. Fill the rows which have blank values in SerialNumber column in Power Query Editor: You can past the below codes in your Advanced Editor

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dZDdCoAgDEZfRbwWyq2eJropBIPSiH5evyxqzcyruaNnH6sqqaWSfpw776gSOgsXWas3B+LA+Wps1/aGKuGWoTGT8C40dX4cHdkKshWpaUgciUM6DXw5+8/4b9p583daOF+ToyRbmeFtw/TuGP+fZifzbAcjH0t/+eod", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [OrderNumber = _t, Item = _t, Description = _t, SerialNumber = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"OrderNumber", Int64.Type}, {"Item", type text}, {"Description", type text}, {"SerialNumber", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"OrderNumber"}, {{"Details", each Table.FillUp(Table.FillDown(_,{"SerialNumber"}),{"SerialNumber"}), type table [#"OrderNumber "=nullable number, #" Item "=nullable text, #" Description "=nullable text, #" SerialNumber"=nullable number]}}),
    #"Expanded Details" = Table.ExpandTableColumn(#"Grouped Rows", "Details", {"Item", "Description", "SerialNumber"}, {"Item", "Description", "SerialNumber"})
in
    #"Expanded Details"

yingyinr_0-1634267627697.png

2. Create a slicer with SerialNumber field

yingyinr_1-1634267749918.png

Best Regards

Community Support Team _ Rena
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

3 REPLIES 3
vpsoini
Helper I
Helper I

Hi.  Thanks for the suggestion.  If I understood right, that solution relies on vehice row being the first , followed by options. Sometimes the order might be different and the option - row gets written before the vehicle info.

 

Is there any solution, which would use the common ordernumber only, despite of the order of the rows?

Hi @vpsoini,

I created a sample pbix file(see attachment), please check whether that is what you want.

1. Fill the rows which have blank values in SerialNumber column in Power Query Editor: You can past the below codes in your Advanced Editor

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dZDdCoAgDEZfRbwWyq2eJropBIPSiH5evyxqzcyruaNnH6sqqaWSfpw776gSOgsXWas3B+LA+Wps1/aGKuGWoTGT8C40dX4cHdkKshWpaUgciUM6DXw5+8/4b9p583daOF+ToyRbmeFtw/TuGP+fZifzbAcjH0t/+eod", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [OrderNumber = _t, Item = _t, Description = _t, SerialNumber = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"OrderNumber", Int64.Type}, {"Item", type text}, {"Description", type text}, {"SerialNumber", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"OrderNumber"}, {{"Details", each Table.FillUp(Table.FillDown(_,{"SerialNumber"}),{"SerialNumber"}), type table [#"OrderNumber "=nullable number, #" Item "=nullable text, #" Description "=nullable text, #" SerialNumber"=nullable number]}}),
    #"Expanded Details" = Table.ExpandTableColumn(#"Grouped Rows", "Details", {"Item", "Description", "SerialNumber"}, {"Item", "Description", "SerialNumber"})
in
    #"Expanded Details"

yingyinr_0-1634267627697.png

2. Create a slicer with SerialNumber field

yingyinr_1-1634267749918.png

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
amitchandak
Super User
Super User

@vpsoini , based on what I got. Fill down in power query can help  SerialNumber

https://docs.microsoft.com/en-us/power-query/fill-values-column

 

or a new column in dax

SerialNumber new = maxx(filter(table, [Ordernumber] = earlier([Ordernumber] && not(isblank([SerialNumber])) ) ,[SerialNumber])

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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