• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Filtering problem with Relationships in Power Query Data Model (Office 365 and Data from SAP)

percybukes

New Member
Hi
I have downloaded SAP transactions / reports and exported the data to different excel files. I don't make any other changes to these files.
I then "get data" from "workbook" and perform usual review to ensure data types etc are all correct.
There is one column with the "order number" that is common in all the downloads and resultant queries. This column is a whole number data type in all the queries.
I created a new query extracting only the order number column from each of the queries and then sorted this column and removed duplicates, to then create my "link query" to be used in the one-to-many relationship data model.
When i then create a pivot table it looks like the relationships don't work - for example:
when i drop the order number from the link query into the rows and then pull for example movement type from another query then all the movement types that exist in the query pull through and show against one specific order (I filtered to see only one order number), even though the data has only one movement type associated with that specific order in the underlying data file (and in SAP).
When is do a pivot table pulling the order number not from the unique link query but from the same query as the movement typer data is in, then i see the order number with only one movement type showing nest to the order - which is correct.

It seems as if the filtering is not working, but i cant figure out why.
Any help or pointers will be appreciated
Thank you
Percy
 

Chihiro

Excel Ninja
Without seeing how your data model is structured and direction and type of relationship. Bit hard to help.

But I'm guessing, that it's due to one of following.
1. You have ambiguity in relationship (bidirectional, many to many, or more than one route connecting related columns).
2. You don't have any value from fact table (many side) in the value field.
 

percybukes

New Member
Without seeing how your data model is structured and direction and type of relationship. Bit hard to help.

But I'm guessing, that it's due to one of following.
1. You have ambiguity in relationship (bidirectional, many to many, or more than one route connecting related columns).
2. You don't have any value from fact table (many side) in the value field.
thank you for responding
my company does not allow me to give any files out
i only have one directional and only one route
i will investigate point 2 further
i will try build similar model and see if i can post that
thank you
 
Top