Forum Discussion
How not to Ignore Values Filtered by Another Table
I have existing report and now I need to and new table without changing old tables.
Here is the peace of the model:
New Table is connected by compozite key company+contract.
Sometimes rows in New table have ContractID = 0, but ManyToMany table does not.
I need to calculate sum of Money column including rows with ContractID = 0.
Expected:
Company
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUfJLzE01VIrViVYygvKMlGJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Company ID" = _t, #"Company Name" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Company ID", Int64.Type}, {"Company Name", type text}})
in
#"Changed Type"
Contracts
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQ0VNJRcjZUitWJVjIyMgJxjMAcY2NjEMdYKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Contract ID" = _t, #"Contract Name" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Contract ID", Int64.Type}, {"Contract Name", type text}})
in
#"Changed Type"
ManyToMany
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTI0NFSK1YlWMgKyjYyMwGxDNLaxsbFSbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Company ID" = _t, #"Contract ID" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Company ID", Int64.Type}, {"Contract ID", Int64.Type}})
in
#"Changed Type"
New
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTI0BJMGBkqxOhARIyMjIGmKJGJsbAwSRxIxAKtQio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Company ID" = _t, #"Contract ID" = _t, Money = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Company ID", Int64.Type}, {"Contract ID", Int64.Type}, {"Money", Int64.Type}})
in
#"Changed Type"
Additional columns:
3 Replies
- parry2kSuper User
Natallia_23 well you cannot work with incomplete data and keep the relationship you want, not sure what else to tell. Good luck!
- parry2kSuper User
Natallia_23 you should change the filter direction of many to many to single direction in the relationship.
✨ Follow us on LinkedIn and to our YouTube channel
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.
- Natallia_23Resolver I
Hello, I can not change old tables and relationships unfortunately.