M code to get value which was active on specific date

[Zyg_D]

This is my biiiiig table. It holds all the changes made to a database table together with the date. 

I don't need to import the whole table. I only need the "Status" value of every "Name" on two specific dates - 20190902 and 20191002. This is how the table should look after importing it: 

What M code would do it? 

[Zyg_D · Accepted solution]

After the whole day of thinking, trial and error, I came up with something acceptable. Thes M code is not as difficult as DAX. I like it. Kudos to @SteveCampbell  who encouraged me to try out UI. 

let
    Source = Excel.Workbook(File.Contents("C:\Temp\PBI_example.xlsx"), null, true),
    Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Table1_Table,{{"Date", Int64.Type}, {"Name", type text}, {"Status", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "DateGroup", each if [Date] <= 20191002 then
   if [Date] <= 20190902
   then 20190902
   else 20191002
   else 0),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Added Custom", {{"Date", type text}}, "en-US"),{"Date", "Status"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"DateStatus"),
    #"Grouped Rows" = Table.Group(#"Merged Columns", {"Name", "DateGroup"}, {{"MaxDateStatus", each List.Max([DateStatus]), type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Grouped Rows", "MaxDateStatus", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"MaxDateStatus.1", "MaxDateStatus.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"MaxDateStatus.1", Int64.Type}, {"MaxDateStatus.2", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"MaxDateStatus.1"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"DateGroup", "Name", "MaxDateStatus.2"})
in
    #"Reordered Columns"

This shows the status only if it was changed from previous timestamp. I will be able to calculate the remaining one status using Power BI. Using this M code I will only import the data which I need, avoiding tons of useless rows. 

[amitchandak]

@Zyg_D , while adding a connection from the database you can give a query(under advance property). For this table create a connection and add the query.

[Zyg_D]

---

@amitchandak wrote:

@Zyg_D, while adding a connection from the database you can give a query(under advance property). For this table create a connection and add the query.

---

Hello, @amitchandak  . Thank you for the screenshot. The primary source of the data is indeed the database. However, I get this data from a Power BI dataflow. This option does not allow me to input SQL query. I suppose, the only option I have is M code? I want to stress that I do not want to import the whole table into my model. 

[SteveCampbell]

You shouldn't need to write the code specifically, load it into the UI and user the filters on the column headers.

[Zyg_D]

---

@SteveCampbell wrote:

You shouldn't need to write the code specifically, load it into the UI and user the filters on the column headers.

---

You may be right. I ask for assistance, because I am failing to create the desired table on my own. It is not a simple filter. It must also take into account data on another column... I tried grouping and sorting on several columns, but so far no luck. I would greatly appreciate your help.