Forum Discussion

Cheryl's avatar
Cheryl
Regular Visitor
5 years ago
Solved

Use Excel or Power BI to transform data for reports

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 ho...
Need Help
Tutorial Requests
  • Cheryl's avatar
    Cheryl
    4 years ago

    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's avatar
Cheryl
Regular Visitor
4 years ago

[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: