Forum Discussion
Power BI Relationship Help
I have 2 excel files. One has all my main data but the data source does not always have a clean Account Name value, and many of my Accounts Number change from year to year. I have another table with Account Names all clean and the Parent Account Numbers. I need Power BI to link the 2 sheets. So for Example my full data set might have Account number 148 and 124 as Adventure Sports and Do Not Use Adventure Sport, the second sheet has 148 and 124 both as Adventure Sports with Parent account 148. So i want to have as a result in my slicer when i add 148 as the parent account it pulls the data from Full Data on 148 and 124.
olindeman You could create a calculated column in the 2nd table to determine the "Account" that you want to use...
Account = IF(Table2[Parent Account] = BLANK(), Table2[Account Number], Table2[Parent Account])
5 Replies
I have 2 excel files. One has all my main data but the data source does not always have a clean Account Name value, and many of my Accounts Number chnage from year to year. I have another table with Account Names all clean and the Parent Account Numbers. I need Power BI to link the 2 sheets. So for Example my full data set might have Account number 148 and 124 as Adventure Sports and Do Not Use Adventure Sport, the second sheet has 148 and 124 both as Adventure Sports with Parent account 148. The result i need in my slicer is that when account number 148 is selected it pulls the data for both 148 and 124.
Hi olindeman,
Could you please share sample data of your scenario and post the expected result here?
Thanks,
Lydia ZhangMain sheet looks like this, this sheet has 30k plus lines a day
Account number Account Name Sale 148 Aventure Sport 3 124 DO NOT USE Adventure Sport 4 34567 Dons Cars 5
Second sheet looks like this has 160 accounts maxAccount number Parent Account Account Name 148 148 Aventure Sport 124 148 Aventure Sport 34567 Dons Cars My goal would be to relate the sheets somehow so that sheet 1 when it sees 124 it knows to attribute those sales to 148, however when nothing is in sheet two it uses sheet ones account number. Like with Dons Cars, it does not have a parent account so i need it to use 34567.
