Hi there
As part of my job I have to come up with a plan to shift production of our published charts and graphics from Microsoft Excel spreadsheets/graphic design, to Power BI. I haven't used Power BI and have only just started working with a Data Model in Microsoft Excel. I would say that I am advanced with Microsoft Excel.
At the moment I have a workbook with worksheet tables each containing a different type of raw data and I have added many many columns to these tables for calculations and data manipulations.
I have linked these tables together in a Data Model, and make PivotTables based on the Data Model.
Then I pass data from the PivotTables to our graphic design section for them to make up the charts and graphics.
I really like using spreadsheets but it is apparent that there will be too much data to be handled soon, e.g. one type of raw data currently has 900,000 rows which is approaching the worksheet maximum of 1,048,576 rows of data and causes Excel to regularly say that there is not enough memory to display the values when I am in that worksheet. I'm sure it doesn't help that I have lots of columns of calculations and data manipulations.
My very rough understanding is that in future, I should be importing all of the raw data worksheets, without adding calculation and data manipulation columns, into a Data Model, and then do the calculation and data manipulation using Data Analysis Expressions (DAX) within the data model. I could then use the Data Model to make PivotTables like I currently do, and also import the data model into Power BI to make graphics.
My very rough understanding is that I need to move away from spreadsheet functions to Data Model DAX. Once I have a Data Model that contains all of the calculations and data manipulations using DAX, then I can handle much larger amounts of input data and use it easily for Power BI and share the data model.
Does that make sense to experienced users? Sorry if I have packed too much into a single post, I'm only expecting broad guidance on high level principles. Could someone let me know if my overall thinking is misguided? Or provide any tips on getting started on my journey from spreadsheets to Data Model/Power BI?
As part of my job I have to come up with a plan to shift production of our published charts and graphics from Microsoft Excel spreadsheets/graphic design, to Power BI. I haven't used Power BI and have only just started working with a Data Model in Microsoft Excel. I would say that I am advanced with Microsoft Excel.
At the moment I have a workbook with worksheet tables each containing a different type of raw data and I have added many many columns to these tables for calculations and data manipulations.
I have linked these tables together in a Data Model, and make PivotTables based on the Data Model.
Then I pass data from the PivotTables to our graphic design section for them to make up the charts and graphics.
I really like using spreadsheets but it is apparent that there will be too much data to be handled soon, e.g. one type of raw data currently has 900,000 rows which is approaching the worksheet maximum of 1,048,576 rows of data and causes Excel to regularly say that there is not enough memory to display the values when I am in that worksheet. I'm sure it doesn't help that I have lots of columns of calculations and data manipulations.
My very rough understanding is that in future, I should be importing all of the raw data worksheets, without adding calculation and data manipulation columns, into a Data Model, and then do the calculation and data manipulation using Data Analysis Expressions (DAX) within the data model. I could then use the Data Model to make PivotTables like I currently do, and also import the data model into Power BI to make graphics.
My very rough understanding is that I need to move away from spreadsheet functions to Data Model DAX. Once I have a Data Model that contains all of the calculations and data manipulations using DAX, then I can handle much larger amounts of input data and use it easily for Power BI and share the data model.
Does that make sense to experienced users? Sorry if I have packed too much into a single post, I'm only expecting broad guidance on high level principles. Could someone let me know if my overall thinking is misguided? Or provide any tips on getting started on my journey from spreadsheets to Data Model/Power BI?