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
kev_bro
Frequent Visitor

Unsure of best method to represent multiple items in one column against another column

 

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 NumberClient NameProduct Product Id Model Manager Model Name Value 
101Client 1Stocks & Shares ISA 10001Manager 001Basic 1 £5,000.00
101Client 1Stocks & Shares ISA 10001Manager 002Managed 1 £10,000.00
101Client 1Pension 10002Manager 001 Basic 2£50,000.00
102Client 2Stocks & Shares ISA 10003 No Manager 1£50,000.00
102Client 2Pension 10004 No Manager 1£50,000.00
103Client 3Pension 1005Manager 002Volatile 1£25,000.00
103Client 3Pension 1005Manager 001Medium Risk £35,000.00
103Client 3Pension 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 ProductCount of Clients Value
13£15000
21£15,000
31£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 NumberClient NameProduct Product Id Model Manager Model Name Value Multi Models # Multiple Models
101Client 1Stocks & Shares ISA 10001Manager 001Basic 1 £5,000.00

Yes

2
101Client 1Stocks & Shares ISA 10001Manager 002Managed 1 £10,000.00Yes2
101Client 1Pension 10002Manager 001 Basic 2£50,000.00No1
102Client 2Stocks & Shares ISA 10003 No Manager 1£50,000.00No1
102Client 2Pension 10004 No Manager 1£50,000.00No1
103Client 3Pension 1005Manager 002Volatile 1£25,000.00Yes3
103Client 3Pension 1005Manager 001Medium Risk £35,000.00Yes3
103Client 3Pension 1005 Low Risk£5,000.00Yes3

 

 

Any help would be appreciated or if you have smarter method of shapping the data , then happy to listen 

 

Many thanks 

 

Kevin 

2 ACCEPTED SOLUTIONS
Jakinta
Solution Sage
Solution Sage

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"

 

View solution in original post

kev_bro
Frequent Visitor

Thanks Jakinta , much appreciated !

View solution in original post

4 REPLIES 4
kev_bro
Frequent Visitor

Thanks Jakinta , much appreciated !

Jakinta
Solution Sage
Solution Sage

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"

 

@Jakinta 

 

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 NumberClient NameProduct Product Id Model Manager Model Name Value 
101Client 1Stocks & Shares ISA 10001Manager 001Basic 1 £5,000.00
101Client 1Stocks & Shares ISA 10001Manager 002Managed 1 £10,000.00
101Client 1Stocks & Shares ISA 10001Manager 001Basic 1 £20,000.00
101Client 1Stocks & Shares ISA 10001Manager 002Managed 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"

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.

Top Solution Authors
Top Kudoed Authors