Forum Discussion
Power BI Desktop query
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
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...
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"
5 Replies
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...
amitchandak lbendlin Ashish_Mathur Ritaf1983
please helpTable 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"
Check the attached pbix where I have achieved this using measures.
