A Quick Way to Make Relationships?

[dcunningham]

Hi Everyone,

Just starting out with Power BI and I have a question. I've got a large amount of data in a large number of tables in Excel which I've imported into Power BI. All these tables have a time column and another column that contains values for a specific measure. All these time values are formatted the same and are in the same increments (10 minutes). I'd like to use Power BI to compare different measures at certain times, but Power BI's auto-detect doesn't recognize that one "Time" column from one table is related to the time column of all the other tables.

I realize that I can manually tell Power BI that these columns are all related using the process outlined here, but this would be a long process and is rife with opportunity for me to make an error because of the number of tables I have. First I'd have to relate the time of Table 1 to the times of Tables 1-10, then the time of Table 2 to the time of Table and Tables 3-10. Does anyone know of a faster way to do this?

Thanks

Dan

 

[ImkeF]

Hi Dan,
I'd recommend a different approach here that I've described in my latest blogpost: How to compare everything with everything in Power BI

If you don't want to / are not able to take the "From-Folder"-method, you need to add the "Source"-column manually to all your tables during your load and perform an append-merge that UNIONs / creates the consolidated source-table:
Append Multiple Tables in Power Query | The White Pages

 

[dcunningham]

Thank you ImkeF, I read through your post and it's what I was looking for. I thought that I might need to reformat my query/data somewhat.