Help. Ranges date filter with PQ

[philongxpct]

I got a prob with client filter between muli ranges of date PQ's filter. That 2 table: Table2 is data to filter, Table3's like conditions needed (startday - endday)

Group by date [TIME] for every client [NAME] or [idtype] was expected to sum amount of [MONEY]. I knew that was easy to filter by PowerPivot, but i couldn't do it with PQ. Help!!!!

View attachment Ranges dates filter.xlsx


............................
Sorry about my terrible English.

 

[Ken Puls]

Sorry, I'm not quite following.

Can you show me a table of what you expect to have at the end? I see the inputs, I'm just not sure what you want to get back out.

 

[philongxpct]

Sorry if it's not clear. Here is a sample what i expect. Thanks Ken! View attachment Result.xlsx

 

[UK_GER]

u can try out this

Hi,

I'm not an expert - but you can try this.
Pls. test by yourself to check if it returns what you want with different data!


// for first table (your row-wise all data table)


let
Quelle = Excel.CurrentWorkbook(){[Name="tabAlldata"]}[Content],
#"Geänderter Typ" = Table.TransformColumnTypes(Quelle,{{"Date", type date}, {"Name", type text}, {"Amount", Int64.Type}})
in
#"Geänderter Typ"


-----------------------------------
// for second table (your filter table)


let
Quelle = Excel.CurrentWorkbook(){[Name="tabFilter"]}[Content],
#"Geänderter Typ" = Table.TransformColumnTypes(Quelle,{{"Name", type text}, {"startdate", type date}, {"enddate", type date}})
in
#"Geänderter Typ"


-----------------------------------
// for result


let
Quelle = Table.NestedJoin(Tabelle1,{"Name"},Tabelle2,{"Name"},"NewColumn",JoinKind.LeftOuter),
#"Erweiterte NewColumn" = Table.ExpandTableColumn(Quelle, "NewColumn", {"Name", "startdate", "enddate"}, {"NewColumn.Name", "NewColumn.startdate", "NewColumn.enddate"}),
#"Hinzugefügte bedingte Spalte" = Table.AddColumn(#"Erweiterte NewColumn", "Custom", each if [Date] >= [NewColumn.startdate] and [Date] <=[NewColumn.enddate] then "in" else "out" ),
#"Gefilterte Zeilen" = Table.SelectRows(#"Hinzugefügte bedingte Spalte", each ([Custom] = "in"))
in
#"Gefilterte Zeilen

Hope this helps ;-)

UK_GER