Forum Discussion
How to calculate value between two queries?
Hi Everyone, It is hard for me to calculate like this. Any help is appreciable. Thank you in advance! Regards, Kelly
Anonymous
Without calculation it won't be possble.
Possibly you want to know how to do it in Power Query:
let Source = Query2, #"Merged Queries" = Table.NestedJoin(Source,{"First Level Group", "Second Level Group"},Query1,{"First Level Group", "Second Level Group"},"Query1",JoinKind.LeftOuter), #"Added Custom" = Table.AddColumn(#"Merged Queries", "Custom value", each [Reference] * Table.RowCount([Query1]), Int64.Type), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Reference", "Query1"}) in #"Removed Columns"
Zubair_Muhammad
Community Champion
Community ChampionHi Anonymous
Add this calculated column in Query 2
Custom Value=
Query2[Reference]
* CALCULATE (
COUNTROWS ( Query1 ),
FILTER (
Query1,
Query1[First Level Group] = Query2[First Level Group]
&& Query1[Second Level Group] = Query2[Second Level Group]
)
)Thanks a lot!
In addition, Could you tell me how to get the data without calcuation? Thanks in advance!
- MarcelBeug
Without calculation it won't be possble.
Possibly you want to know how to do it in Power Query:
let Source = Query2, #"Merged Queries" = Table.NestedJoin(Source,{"First Level Group", "Second Level Group"},Query1,{"First Level Group", "Second Level Group"},"Query1",JoinKind.LeftOuter), #"Added Custom" = Table.AddColumn(#"Merged Queries", "Custom value", each [Reference] * Table.RowCount([Query1]), Int64.Type), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Reference", "Query1"}) in #"Removed Columns"