Power BI Desktop query

[krishma]

There are two tables which are mapped on the basis of name:
table 1                           

name	type
ram	a
shaym	b
kiran	a
anglea	b
david	c
angha	a
arpitha	b
ram	b
kiran	c
david	a

Table 2

name	money
ram	200
shaym	300
kiran	500
anglea	600
david	700
angha	800
arpitha	100

When i am doing left join on the bass of name, it gives me

name	type	money
ram	a	200
shaym	b	300
kiran	a	500
anglea	b	600
david	c	700
angha	a	800
arpitha	b	200
ram	b	200
kiran	c	500
david	a	700

but i want output like this: total money for that name should be divided equally for each type for ex: Ram has two types a and b and money is 200 so in the final table it should be like
Ram a 100
ram b 100

below output is what i am looking for

name	type	money I want to come
angha	a	800
anglea	b	600
arpitha	b	200
david	c	350
david	a	350
kiran	a	250
kiran	c	250
ram	a	100
ram	b	100
shaym	b	300

can someplease please help me

[jgeddes · Accepted solution]

You can do this in Power Query. 

Group the the table with the types by name choosing all rows to get a nested table per name.

Merge by Name with the money query.

Expand the merge to get the money column

Add a column to get the row count of the nested tables. 

Table.AddColumn(PREVIOUSSTEP, "nestedRowCount", each Table.RowCount([_nestedTable]), Int64.Type)​

Replace the values in the money column with the money value divided by the nested row count.

Table.ReplaceValue(PREVIOUSSTEP,each [money],each [money]/[nestedRowCount],Replacer.ReplaceValue,{"money"})

​Remove the nestedRowCount column

expand the nested table type column only.

You should get something like...

[miTutorials]

Check the attached pbix where I have achieved this using measures.

[jgeddes · Accepted solution]

You can do this in Power Query. 

Group the the table with the types by name choosing all rows to get a nested table per name.

Merge by Name with the money query.

Expand the merge to get the money column

Add a column to get the row count of the nested tables. 

Table.AddColumn(PREVIOUSSTEP, "nestedRowCount", each Table.RowCount([_nestedTable]), Int64.Type)​

Replace the values in the money column with the money value divided by the nested row count.

Table.ReplaceValue(PREVIOUSSTEP,each [money],each [money]/[nestedRowCount],Replacer.ReplaceValue,{"money"})

​Remove the nestedRowCount column

expand the nested table type column only.

You should get something like...

[krishma]

Thank you so much it helped me alot.
@lbendlin  @jgeddes 

[krishma]

@amitchandak @lbendlin  @Ashish_Mathur @Ritaf1983 
please help

[lbendlin · Accepted solution]

Table 1

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKkrMVdJRSlSK1YlWKs5IrATxksC87MyixDy4XGJeek5qIlwyJbEsMwXIS4ZJZiQilBYVZJZkINRCrEA1NBnFFKC+WAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [name = _t, #"type" = _t]),
    #"Added Custom" = Table.AddColumn(Source, "money I want to come", (k)=> Table.SelectRows(#"Table 2",each [name]=k[name]){0}[money]/List.Count(List.Select(Source[name],each _=k[name])))
in
    #"Added Custom"