Use Excel or Power BI to transform data for reports

[Cheryl]

I have used PowerBI to create reports quite a bit, but with simple one-table models. I did all my transformations in Excel each week, mostly with formulas keeping the same table name (I don't know how to write the formulas in PQ language) and pasting the new data over the old data, and then got the data in PowerBI Desktop and published it to the service, and then updated decimal places and checked column types. After I uploaded the new file, I would delete the old query and remove the number from the newly uploaded query and the report updated beautifully. I got the process down to under 30 minutes.

On a new project, I now have 14 tables and 5 pages of visuals. 4 of the tables are updated twice a week. I thought it would be more efficient if I did the transformations in Power BI, but my old method of uploading the data doesn't make me more efficient because I delete the old query and rename the new query to the old name, losing all my data cleanup. (duh, I guess)

I'm unsure about using Power Query in Excel...I don't understand how my pasting-over method would work with a table that has columns removed in the query. Do I start the query in a blank workbook and get the data from another file so that the query is saved to the new workbook? Obviously I've been using ...from a table when I've used PQ in Excel and built the whole thing in one workbook. Maybe that's the answer, but when I upload the file, I would still be deleting the old query, so I'd still have to reset column types and decimal places. But if that's all I had to do, it's straightforward.

One final question, is it better (easier) to put all your tables in one workbook. Obviously my files aren't huge, from 200 to 2000 rows. I was thinking that would help keep me organized so I don't have to hunt all over for the tables. I could just pick and choose which ones I load in the query. Is that thinking right?

Thank you for any clarification you can provide for me, or a good resource that covers tips and things to avoid doing when you are revising and reuploading data.

Cheryl

[Cheryl · Accepted solution]

OMG. I just figured it out on my own. All I would have ever had to do for the past year and a half when I was working on the other dashboard and now this one is (wait for it) hit Refresh. I can't believe all the gyrations I was going through to update the data. Well, better late than never?

Thanks!

Cheryl

[Cheryl]

[image of model at bottom] I went thought the whole process this weekend. I did copy my new data over my old data in my workbook that I upload to the desk top so that the table names don't change. I select just the table that I want to update from it, thinking it would just replace the source in the query for that table that is already there. Instead, if the first query was PediatricData, the newly uploaded data is PediatricData_2 or something like that. So in my model, I have a new table with a bunch of connections that I have to get rid of, I have to delete the original table, and reestablish its connections to the new table. Any transformations I've done in the desktop are lost, so I'm trying to do as many of them as I can in Excel so I won't have to worry about doing that over too. 

Some of the transformations are things like removing a column, creating a target with a simple multiplication formula, changing dates to text and replacing the /single digit/ with /0X/ so they will sort correctly (I haven't had a chance to study date tables yet and it's driving me crazy), Nothing too crazy that a beginner can't handle.

The tables that I'll be replacing regularly are:

1. Active_School_Cases_Table008_2 (2) [and I'd like to have the name just have it end at Table008)

2. T_CoDataThisWeek (2)

3. T_CountyTransXTime11 (2)

4. County_Pediatric_Query10 (2)

Thank you for any help you can provide. I know there has to be a more efficient way to do this. {model and report image below]

Thank you!

Cheryl

There are a few hanging out on the right that I haven't needed yet. They may be deleted later.Working Model

Page that these tables update:

[lbendlin]

Are you ready to move on from your single-table models to more complex data models with multiple tables and their relationships, ideally in a star schema?

Which of your tables have content that changes slowly or never?  

What kinds of transforms are you doing in your Excel files?