Create table (not a pivot table) from Data Model
I have set up the data in my data model. Used Power Query Editor for ETL. I set up the relationships in the model with a fact table and my 3 dimension tables. Now I want to create a data table in a worksheet that that merges columns from each of the tables. I know I can use the merge feature in power query, but I am working with large data and the load time is ridiculously slow. What we often do if each table does not exceed 1 million rows, is load the tables into a worksheet. Then open MS Access and link to the Excel tables and create queries to merge data and create a table. Then we end up exporting that table back into Excel so we can share that workbook within our organization without the links. But this seem archaic to me. Surely there is a better way?
I have set up the data in my data model. Used Power Query Editor for ETL. I set up the relationships in the model with a fact table and my 3 dimension tables. Now I want to create a data table in a worksheet that that merges columns from each of the tables. I know I can use the merge feature in power query, but I am working with large data and the load time is ridiculously slow. What we often do if each table does not exceed 1 million rows, is load the tables into a worksheet. Then open MS Access and link to the Excel tables and create queries to merge data and create a table. Then we end up exporting that table back into Excel so we can share that workbook within our organization without the links. But this seem archaic to me. Surely there is a better way? Read More