Forum Discussion
Excel VLOOKUP against Power BI entity
Hello everyone,
I have a Power BI custom table containing all comprehensive information and it consists of 1.4mln rows. I would like to somehow link this Power BI table to an excel spreadsheet and to have an ability to copy/paste exact part numbers (for example, 10 diverse items) and want to automatically lookup (or merge) and bring all the information ralated only to this 10 items from Power BI table instead of searching each one by one. Please advise on the easiset way to achieve this as I want to train business users with lack of techical and BI knowledge to execute this on a daily basis.
You can make an Excel table into which you paste your 10 search items, let's say 10 Account Numbers. Use get data from table or range to add this table to power query, and then make it a list. Use that list in a Table.SelectRows(PriorStepOrTableName, each List.Contains(List.Buffer(NameOfYourUpdatedList), [Account Number]))
Assuming that you'll be loading the resulting table to an Excel sheet, you can just update your list in Excel, then just click Refresh all.
--Nate
4 Replies
You can make an Excel table into which you paste your 10 search items, let's say 10 Account Numbers. Use get data from table or range to add this table to power query, and then make it a list. Use that list in a Table.SelectRows(PriorStepOrTableName, each List.Contains(List.Buffer(NameOfYourUpdatedList), [Account Number]))
Assuming that you'll be loading the resulting table to an Excel sheet, you can just update your list in Excel, then just click Refresh all.
--Nate
Awesome solution! Thanks a lot!
You can run DAX queries against the Power BI Semantic model and return the results in Excel as table queries. Very much like what Export...Excel with live connection does.
You can use the Merge feature in Power Query also
