Moving from spreadsheet to data model/Power BI

[Stuartzz]

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?

 

[Matt Allington]

See below


[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.]

as you mention later - don’t do this. The general rule is “don’t touch your source data, just connect and load it”. If you touch it, you are probably doing it wrong

the Excel data model is ok, but PBI is better. If you are just passing pivot tables to someone else, it’s fine. Keep a separate data model workbook with no data in sheets; just pivot tables. This way, you can have 200m+ rows, no problems

[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.]

yes, there is a row limit, but also a column issue. Columns are way worse in Power Pivot than rows (not withstanding the row limit). It depends on your source. Maybe you can use csv as the source.

[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.]

yes, but DAX can be calculated columns, or measures. You want measures. Calculated Columns vs Measures in DAX

[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.]

yes, as long as you write measures and not columns.

[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?]

100%. You are on track.

 

[Stuartzz]

[the Excel data model is ok, but PBI is better. If you are just passing pivot tables to someone else, it’s fine. Keep a separate data model workbook with no data in sheets; just pivot tables. This way, you can have 200m+ rows, no problems]

Thanks so much for your response Matt, very helpful. When you mentioned 'Excel Data Model', are you referring to:

• my clunky system of spreadsheet data tables with added calculation columns that are then imported into Power Pivot Data Model, or
• Power Pivot Data Models in general?

I was just hoping to clarify because it may affect strategy. Currently aim is to have a Microsoft Excel workbook that just contains:

• a Power Pivot Data Model containing data imported from worksheets in other workbooks
• DAX-based measures in that Power Pivot Data Model
• PivotTables in worksheets that are entirely derived from the Power Pivot Data Model

and once that is all set up, export that Power Pivot Data Model with the DAX-based measures, into Power BI for doing visualisations.

I'm working on the assumption that the Data Model with the DAX-based measures can be created in either Power Pivot in Microsoft Excel or Power BI, and that I can transfer the Data Model easily between Microsoft Excel to do PivotTables, and Power BI to do visualisations. For my purposes, is there a difference between a Data Model created in Power Pivot in Microsoft Excel and one created in Power BI?

 

[Matt Allington]

When I say data model, I mean the Power Pivot data model with tables and relationships. I am not referring to the source data. It is best practice to keep your source data and data model in different workbooks. It is easy to migrate from Excel Power Pivot to Power BI, but not as easy the other way.

 

[Stuartzz]

Okay thanks very much.