Forum Discussion

gehe_muc's avatar
gehe_muc
Frequent Visitor
4 years ago

Split rows in news rows with a new dimension depending from values in another table

Hello everybody,   I have a table with values (revenue, margin, ...) and want to split the rows depending on the values (share%) of another table. Should I build a new table, or split the rows in t...
Need Help
wdx223_Daniel's avatar
wdx223_Daniel
Icon for Community Champion rankCommunity Champion
4 years ago

if you did not mind the floating difference, then try this code

=#table({"Month","City","Revenue","Margin"},List.TransformMany(Table.ToRows(Table1),each Table.ToRows(Table2),(x,y)=>{x{0},y{0},x{1}*y{1},x{2}*y{1}}))

otherwise, try this

=let a=Table.ToRows(Table2),b=List.Count(a)-1 in #table({"Month","City","Revenue","Margin"},List.TransformMany(Table.ToRows(Table1),each List.Accumulate(List.Positions(a),{{},_{1},_{2}},(x,y)=>if y=b then {x{0}&{{_{0},a{y}{0},x{1},x{2}}}} else {x{0}&{{_{0},a{y}{0},_{1}*a{y}{1},_{2}*a{y}{1}}},x{1}-_{1}*a{y}{1},x{2}-_{2}*a{y}{1}}),(x,y)=>y{0}))