Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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"
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.