Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Afternoon All,
Trying to contemplate the best method of trying to either setup through a new column or using a calcualtion method . Below is the table of data I have and I am trying to highlight the following information
1) Clients holding muliple Models (based on model name) in the same product value
2) Clients with multiple models in same product & the number of models
Client Number | Client Name | Product | Product Id | Model Manager | Model Name | Value |
101 | Client 1 | Stocks & Shares ISA | 10001 | Manager 001 | Basic 1 | £5,000.00 |
101 | Client 1 | Stocks & Shares ISA | 10001 | Manager 002 | Managed 1 | £10,000.00 |
101 | Client 1 | Pension | 10002 | Manager 001 | Basic 2 | £50,000.00 |
102 | Client 2 | Stocks & Shares ISA | 10003 | No Manager 1 | £50,000.00 | |
102 | Client 2 | Pension | 10004 | No Manager 1 | £50,000.00 | |
103 | Client 3 | Pension | 1005 | Manager 002 | Volatile 1 | £25,000.00 |
103 | Client 3 | Pension | 1005 | Manager 001 | Medium Risk | £35,000.00 |
103 | Client 3 | Pension | 1005 | Low Risk | £5,000.00 |
So from the above example I know we have 2 clients holding the same products with multiple models and a total value of £80,000
if i wanted to cut that data even further we also know the data could look like this
Count of models in Product | Count of Clients | Value |
1 | 3 | £15000 |
2 | 1 | £15,000 |
3 | 1 | £65,000 |
Considered the option of adding another column which yes / no flag if there is more than one model on the clients product ( based against product id ) and or potentially adding a column which has a count of models against the product . How would I go about adding the extra columns in bold below ?
Client Number | Client Name | Product | Product Id | Model Manager | Model Name | Value | Multi Models | # Multiple Models |
101 | Client 1 | Stocks & Shares ISA | 10001 | Manager 001 | Basic 1 | £5,000.00 | Yes | 2 |
101 | Client 1 | Stocks & Shares ISA | 10001 | Manager 002 | Managed 1 | £10,000.00 | Yes | 2 |
101 | Client 1 | Pension | 10002 | Manager 001 | Basic 2 | £50,000.00 | No | 1 |
102 | Client 2 | Stocks & Shares ISA | 10003 | No Manager 1 | £50,000.00 | No | 1 | |
102 | Client 2 | Pension | 10004 | No Manager 1 | £50,000.00 | No | 1 | |
103 | Client 3 | Pension | 1005 | Manager 002 | Volatile 1 | £25,000.00 | Yes | 3 |
103 | Client 3 | Pension | 1005 | Manager 001 | Medium Risk | £35,000.00 | Yes | 3 |
103 | Client 3 | Pension | 1005 | Low Risk | £5,000.00 | Yes | 3 |
Any help would be appreciated or if you have smarter method of shapping the data , then happy to listen
Many thanks
Kevin
Solved! Go to Solution.
Hi,
You can paste this in blank query
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("pdLPC4IwFAfwf+Wxs8R+tO7VKaiIhC7mYeiooW3glP793ERFhbI67Y33+OzLtihCBBMUoG2upC7BlWFpkszCtcKYriC8i0Ja2IVrqHsEYz9+EFrcZAHNbiOsSoC4AR7UEwuMURz8TdNulzY4wW/1k9RWGd1idJgTuqCuwUcU7Sk6IyirV1cfDbRHkM/qKOByHsJ6hE0QPrmxi8lFqXLpLcp/tvxbyFRVDzgrm7kZ9q3myr15emDwOeIX", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Client Number" = _t, #"Client Name" = _t, #"Product " = _t, #"Product Id " = _t, #"Model Manager " = _t, #"Model Name " = _t, #"Value " = _t]),
#"Grouped Rows" = Table.Group(Source, {"Client Number", "Client Name", "Product "}, {{"All", each _, type table }, {"Multi Models", each if Table.RowCount(_)>1 then "Yes" else "No", Int64.Type}, {"#Multiple Models", each Table.RowCount(_), Int64.Type}}),
#"Expanded All" = Table.ExpandTableColumn(#"Grouped Rows", "All", {"Product Id ", "Model Manager ", "Model Name ", "Value "}, {"Product Id ", "Model Manager ", "Model Name ", "Value "})
in
#"Expanded All"
Thanks Jakinta , much appreciated !
Hi,
You can paste this in blank query
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("pdLPC4IwFAfwf+Wxs8R+tO7VKaiIhC7mYeiooW3glP793ERFhbI67Y33+OzLtihCBBMUoG2upC7BlWFpkszCtcKYriC8i0Ja2IVrqHsEYz9+EFrcZAHNbiOsSoC4AR7UEwuMURz8TdNulzY4wW/1k9RWGd1idJgTuqCuwUcU7Sk6IyirV1cfDbRHkM/qKOByHsJ6hE0QPrmxi8lFqXLpLcp/tvxbyFRVDzgrm7kZ9q3myr15emDwOeIX", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Client Number" = _t, #"Client Name" = _t, #"Product " = _t, #"Product Id " = _t, #"Model Manager " = _t, #"Model Name " = _t, #"Value " = _t]),
#"Grouped Rows" = Table.Group(Source, {"Client Number", "Client Name", "Product "}, {{"All", each _, type table }, {"Multi Models", each if Table.RowCount(_)>1 then "Yes" else "No", Int64.Type}, {"#Multiple Models", each Table.RowCount(_), Int64.Type}}),
#"Expanded All" = Table.ExpandTableColumn(#"Grouped Rows", "All", {"Product Id ", "Model Manager ", "Model Name ", "Value "}, {"Product Id ", "Model Manager ", "Model Name ", "Value "})
in
#"Expanded All"
I have notice I have not included where there could be duplicate rows , for insance where client 1 may hold duplicate rows of date
Client Number | Client Name | Product | Product Id | Model Manager | Model Name | Value |
101 | Client 1 | Stocks & Shares ISA | 10001 | Manager 001 | Basic 1 | £5,000.00 |
101 | Client 1 | Stocks & Shares ISA | 10001 | Manager 002 | Managed 1 | £10,000.00 |
101 | Client 1 | Stocks & Shares ISA | 10001 | Manager 001 | Basic 1 | £20,000.00 |
101 | Client 1 | Stocks & Shares ISA | 10001 | Manager 002 | Managed 1 | £20,000.00 |
So below the current logic create an output of 4 multimodels but in theory there are only 2 . Any way the solution could be changed to consider that aspect as well ?
Probably not what you want, but give a try anyway...
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rdLPC4IwFAfwf+XhWeJta92rU1ARCV3Mw9BRQ9vAKf37uYlJCmE/TntjXz7vbSyOA4IkCIN1oaSuwJVRZdLcwrlGpAuIrqKUFjbREpozgujjO6HFRZbQ7lbCqhSIC/CwScwQgyT8mabPXdbiBP+oDwan/7RHkw912ut0gs6a1dV7A10T15C/Vw9SW2V0h8ynIaxH2Ajho2ueTCEqVUhvUf615R9QZqq+wVHZ3GXYp5ort+bugZe/mDwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Client Number" = _t, #"Client Name" = _t, #"Product " = _t, #"Product Id " = _t, #"Model Manager " = _t, #"Model Name " = _t, #"Value " = _t]),
#"Grouped Rows" = Table.Group(Source, {"Client Number", "Client Name", "Product ","Model Name "}, {{"All", each _, type table }, {"Multi Models", each if Table.RowCount(_)>1 then "Yes" else "No", Int64.Type}, {"#Multiple Models", each Table.RowCount(_), Int64.Type}}),
#"Expanded All" = Table.ExpandTableColumn(#"Grouped Rows", "All", {"Product Id ", "Model Manager ", "Value "}, {"Product Id ", "Model Manager ", "Value "}),
#"Reordered Columns" = Table.ReorderColumns(#"Expanded All",{"Client Number", "Client Name", "Product ", "Product Id ", "Model Manager ", "Model Name ", "Value ", "Multi Models", "#Multiple Models"})
in
#"Reordered Columns"