Load SQL database tables with different dates automatic

[lasoe]

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

[v-haibl-msft]

@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]

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]

@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

[Barry_Kelly]

Date.IsInPreviousNDays([Text Range], 2)

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