Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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
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
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
Date.IsInPreviousNDays([Text Range], 2)
Can the day parameter "2" be set by a function or query?
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
86 | |
84 | |
83 | |
67 | |
49 |
User | Count |
---|---|
131 | |
111 | |
96 | |
71 | |
67 |