Create dynamic table for cost allocation

Hi rodiba ,

Please try:

Duplicate Table A

Then Append Queries(Table A left outer join Table B)

Expand Table B

Add custom column

remove other columns

Here is the M code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUByIjAyNjJR0lp/wkIGloYKAUqwOUMkaSCsnPBZJmUBlTDE0gmVgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, User = _t, Cost = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"User", type text}, {"Cost", Int64.Type}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"User"}, #"Table B", {"Assigned to"}, "Table B", JoinKind.LeftOuter),
    #"Expanded Table B" = Table.ExpandTableColumn(#"Merged Queries", "Table B", {"File", "Assigned to"}, {"Table B.File", "Table B.Assigned to"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Table B", "Custom", each [Cost]/
Table.RowCount(
Table.SelectRows(#"Table B",(x)=>x [Assigned to]=[User]))),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Cost", "Table B.Assigned to"})
in
    #"Removed Columns"

Final output:

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.