Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
lasoe
New Member

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

4 REPLIES 4
v-haibl-msft
Microsoft Employee
Microsoft Employee

@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

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

 

@lasoe

 

Assuming we have some tables in SQL Server like below.

Load SQL database tables with different dates automatic_1.jpg

 

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.

Load SQL database tables with different dates automatic_2.jpg

 

Best Regards,

Herbert

Date.IsInPreviousNDays([Text Range], 2)

Can the day parameter "2" be set by a function or query?

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.