Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 🙂
Solved! Go to 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"
2. Create a slicer with SerialNumber field
Best Regards
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"
2. Create a slicer with SerialNumber field
Best Regards
@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])
User | Count |
---|---|
141 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
123 | |
101 | |
71 | |
61 |