Forum Discussion

BBL's avatar
BBL
Frequent Visitor
7 years ago

Load multiple tables in Power Query

Hello everyone,

 

I'm new to this community so I hope that I got the location right..

New in PowerBI, despite a few hours of research I can not find the solution.

I want to load multiple tables from my database with Power Query.

 

Situation :

Every end of the day, my table TAB which contains the daily productions data is archived with the name TAB_YYYY_MM, with YYYY curent year and MM curent month.
All the data of the month MM are stored in this table, for example for April 2019, the archive will be TAB_2019_04, for June-2018 => TAB_2018_06

 

 

With this concept, can someone help me how to load the rolling 12 months tables of history from curent date, in a dynamic way of course.
For today for example, it will be tables TAB_2018_04, TAB_2018_05, ..., TAB_2019_04 and TAB

 

Thanks for your help.

Need Help

3 Replies

  • v-frfei-msft's avatar
    v-frfei-msft
    Community Support
    7 years ago

    Hi BBL ,

     

    We can use DateTime.LocalNow() and Date.AddYears(DateTime.LocalNow(),-1) to work on it. Here I just filter the data by rows in a table for your reference, not tables. M code for your reference.

     

    let
    Source = Sql.Databases("localhost"),
    Franktest = Source{[Name="Franktest"]}[Data],
    dbo_Product = Franktest{[Schema="dbo",Item="Product"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(dbo_Product,{{"SalesDate", type datetime}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([SalesDate] <= DateTime.LocalNow() and [SalesDate] >=Date.AddYears(DateTime.LocalNow(),-1)))
in
    #"Filtered Rows"

     

    • BBL's avatar
      BBL
      Frequent Visitor
      7 years ago

      v-frfei-msft 

      Except the data are not in the same table.
      And that's my question, how will I dynamically load the rolling 12 months tables, and daily productions data table TAB?