Forum Discussion

lasoe's avatar
lasoe
New Member
10 years ago

Load SQL database tables with different dates automatic

Hi

 

I have a database that generates a new table every day.

The name on one of the table is.

cdr_ty_20160819_act

I would like to make a M script that loads today and the last 30 days.

How to do that in M.

 

Regards

Lars

Need Help

4 Replies

  • v-haibl-msft's avatar
    v-haibl-msft
    Microsoft Employee
    10 years ago

    lasoe

     

    Just to confirm, how many dataset (Queries) do you want to get in Query Editor, 31 tables or one consolidated table?

     

    Best Regards,

    Herbert

    • lasoe's avatar
      lasoe
      New Member
      10 years ago

      Hi

       

      It would be great to have it in one consolidated table. But 31 tables will also do the job.

      I have only read access to the SQL, so i can't do it on the SQL server.

       

      Regards

      Lars

       

      • v-haibl-msft's avatar
        v-haibl-msft
        Microsoft Employee
        9 years ago

        lasoe

         

        Assuming we have some tables in SQL Server like below.

         

        We can paste following Power Query into Advanced Editor.

        let
    Source = Sql.Databases("HERBERTSQL2016"),
    cdr_ty = Source{[Name="cdr_ty"]}[Data],
    #"Removed Columns" = Table.RemoveColumns(cdr_ty,{"Schema", "Item", "Kind"}),
    #"Expanded Data" = Table.ExpandTableColumn(#"Removed Columns", "Data", {"ID", "Sales"}, {"Data.ID", "Data.Sales"}),
    #"Inserted Text Range" = Table.AddColumn(#"Expanded Data", "Text Range", each Text.Middle([Name], 7, 8), type text),
    #"Changed Type" = Table.TransformColumnTypes(#"Inserted Text Range",{{"Text Range", type date}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each Date.IsInPreviousNDays([Text Range], 2) or Date.IsInCurrentDay([Text Range]))
in
    #"Filtered Rows"

        We can get one table with following format, which includes data of today and last two days.

         

        Best Regards,

        Herbert