Search code examples
powerbi

PowerBI Join Tables Based on Index Column and Name of Tables

I'm using PowerBi (desktop version). I have a table with a list 102 library names and an index column, like so:

Library Index
Lib1 1
Lib2 2

I have a 102 separate tables containing the opening and closing hours of each library. As you can see below, all the names of tables are formatted in this format Hours (1), where the number in the parentheses correspond to the index number in the library table above. For reference, the data in the hours table is like this:

DOW Opening Closing
Mon 9 20
Tues 9 20

The join I'm hoping to create is something along the lines of this, where each library will be repeated 7 times.

Library DOW Opening Closing
Lib1 Mon 9 20
Lib1 Tues 9 20
Lib1 Wed 9 20
Lib1 Thurs 9 20
Lib1 Fri 9 20
Lib1 Sat 9 17
Lib1 Sun 9 12

Any thoughts on how to create this type of join?

enter image description here

Solution

  • To answer your specific question, add a Custom Column to your first table with the following:

    Expression.Evaluate("#""Hours (" & Number.ToText([Index]) & ")""", #shared)

    Then after that, you can expand this new column. Custom Column

    I am curious though, why you have a query of hours per library? What is the source of each of these queries? (Web Query?). I ask since you can probably solve this without having 102 queries, and instead use a Power Query Function in your first table.