In regards to best practices when setting up a Data Model, when the excel files you are working with contain multiple vlookup and xref columns. How does PowerBI handle the upload of that vlookup & xref data?
Some potential solutions I’ve come across are
1.Creating a new excel, copying and pasting all the data as values then importing that into Power Bi. (though I feel this leads into more trouble in the future)
2.Importing the basefile and creating multiple LOOKUP columns based on reference tables imported into PowerBi to mimic what is done in Excel.
Is there an option I’m missing or a general best practice for situations like these? Thanks in advance!
When you import the data, my understanding is that it’s importing values, not formulas. That being said, ensure that your data is flat before importing. No outlines, matrices, or pivot tables.
I second everything @mickeydjw says above. In addition, here are two excellent videos you may find helpful that speak directly to best practices for building a proper data model from a flat file import:
Hi @kurzashane, we’ve noticed that no response has been received from you since 21st of February. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!
Hi, I guess I’m still a little foggy after reviewing the material. Wouldn’t importing the base file without the lookup columns and then adding them in query editor be the more conducive route? That way when any new data is entered to the file I’m not having to worry about copying down formulas or having to mess with them at all?
It’s difficult to make any strong recommendations without seeing more of the specifics of your situation, but generally speaking in this situation I pull in the main flat file (fact table) and the xref tables (dimension tables) separately, build the relationships (always manually, never let Power BI do this automatically) and then move the relevant fields from my flat file to my dimension tables, per the techniques in the videos).
You shouldnt worry about copying formulas at the data modeling stage. As @mickeydjw mentioned, these get pulled in as values. Once you have a good data model built, the relationships/relational structure may render the need for VLOOKUP moot, and if not, it’s simple to replicate in a measure using LOOKUPVALUE.
