Power Query - create a column with values based on comparing two tables dates

[Ricardo_boy_ser]

Hi,

I need to create a calculated column in which every row can have a different value but every one has the same logical operation.

I have two tables:
TABLE-A has the data info which contains 3 columns [Date(dd/mm/yyyy); Item, POSretail]

TABLE-B has the calendar info in 3 columns [Year; YTD from, YTD to]

the new column would be at the right side of TABLE-A with the name "YTD", the operation needs to compare the TABLE-A [YEAR(Date)] on TABLE-B in order to look for the values "YTD FROM" and "YTD TO", for example:

if my date in TABLE-A is "17/03/2019" the year is "2019" so my "YTD FROM" is "07/01/2019" and "YTD TO" is "04/04/2019"

with that info i can compare if my TABLE-A (date) is between "YTD FROM" and "YTD TO", in excel i would use the next formula:

=IF(  AND(  Date>=VLOOKUP(YEAR(Date),TABLE-B[YEAR:YTD TO,2,0), Date<=VLOOKUP(YEAR(Date),TABLE-B[YEAR:YTD TO,3,0) ),POSretail,0)

substituting values would be:

= IF( AND( (17/03/2019) >= (07/01/2019), (17/03/2019) <= (04/04/2019), $24, $0)

for this example the if bool value would be "TRUE" so i would have in that row the vale $24.00, note that this value is on the same row of the Date in table A in column "POS retail".

the real question here is how could i do this on Power Query?

Best regards,

Ricardo

[edhans · Accepted solution]

Ok @Ricardo_boy_ser see if this works. This is my result - I filtered out the 0's for this screenshot so you could see your data matched mine - the last two columns:

Now, I want to explain why I did it the way I did. On Planet Excel you'd just do a vlookup like you have, and all is good. Power Query doesn't work that way though. It does everything row by row. But the Excel way might look like this:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"DATE", type date}, {"ITEM", type text}, {"POS RETAIL", Currency.Type}, {"MTD", Currency.Type}, {"YTD", Currency.Type}}),
    YearMatch =
        Table.AddColumn(
            #"Changed Type",
            "Year Match",
            each 
                let
                    varLookupDate = [DATE]
                in
                    Table.RowCount(
                        Table.SelectRows(
                            Calendar,
                            each [YTD FROM] < varLookupDate and [YTD TO] > varLookupDate
                        )
                )
        ),
    #"Filtered Rows" = Table.SelectRows(YearMatch, each ([Year Match] = 1))
in
    #"Filtered Rows"

That will return a 1 or 0 depending on whether or not the date is in the date range desired. But when i went to load that, it got to 5,000 records very quickly, then started to drag, and I cancelled it. I knew that would be the result. You can still see this in the file below in the Data (2) table. But don't use that logic.

What I did was this:

Your Calendar table had start/end ranges. I converted those to a list of numbers, expanded them, then to dates using this code:

let
    Source = Calendar,
    #"Added Year Range" = Table.AddColumn(Source, "Year Range", each {Number.From([YTD FROM])..Number.From([YTD TO])}),
    #"Expanded Year Range" = Table.ExpandListColumn(#"Added Year Range", "Year Range"),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Year Range",{{"Year Range", type date}})
in
    #"Changed Type"

It returned this table:

I then merged that column with the Date column in your Data table. If there is a match, pull the POS, if not, return 0. That is this code in the Data table, which starts with your Excel file.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"DATE", type date}, {"ITEM", type text}, {"POS RETAIL", Currency.Type}, {"MTD", Currency.Type}, {"YTD", Currency.Type}}),
    #"Merged Calendar Year Range" = Table.NestedJoin(#"Changed Type", {"DATE"}, #"Calendar Year Range", {"Year Range"}, "Calendar Year Range", JoinKind.LeftOuter),
    #"Expanded Calendar Year Range" = Table.ExpandTableColumn(#"Merged Calendar Year Range", "Calendar Year Range", {"YEAR"}, {"YEAR"}),
    #"Added New Year POS Retail" = Table.AddColumn(#"Expanded Calendar Year Range", "New Year POS Retail", each if [YEAR] = null then 0 else [POS RETAIL], Currency.Type),
    #"Merged Calendar Month Range" = Table.NestedJoin(#"Added New Year POS Retail", {"DATE"}, #"Calendar Month Range", {"Month Range"}, "Calendar Month Range", JoinKind.LeftOuter),
    #"Expanded Calendar Month Range" = Table.ExpandTableColumn(#"Merged Calendar Month Range", "Calendar Month Range", {"YEAR"}, {"YEAR.1"}),
    #"Added New Month POS Retail" = Table.AddColumn(#"Expanded Calendar Month Range", "New Month POS Retail", each if [YEAR.1] is null then 0 else [POS RETAIL], Currency.Type),
    #"Removed Other Columns" = Table.SelectColumns(#"Added New Month POS Retail",{"DATE", "ITEM", "YTD", "MTD", "New Year POS Retail", "New Month POS Retail"})
in
    #"Removed Other Columns"

Then I did it again for the Month ranges.

This returned the desired results for all 270K records as fast as Excel could fill the cells.

Here is your Excel file back with all queries. Nothing I did is "loaded" because it would just bloat the file size, but you can load the Data table and see the true results. Same thing as if you loaded it to Power BI's Data model. But leave the two calendar tables I created as NOT LOADED - they are just used for processing.

Your Excel file.

[Ricardo_boy_ser]

Thank you very much @edhans it works really good.

very clever way to solve the problem.

[edhans]

Glad I was able to assist @Ricardo_boy_ser 

[edhans]

It would be really helpful @Ricardo_boy_ser if you would include some data that we can use (see links below) as well as expected output - there an image of a mockup if what you want in Excel.

I think this will involve nested tables using Table.SelectRows, which may be ok on small data sets but will perform horribly on larger data sets. How many records are these tables? I don't want to spend time keying in all of that data above if I am not understanding the need or the output clearly.

How to get good help fast. Help us help you.

How To Ask A Technical Question If you Really Want An Answer

How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.

[Ricardo_boy_ser]

Hi Edhans,

sure, you´ll find a mock on the wetransfer link below, in that file you will notice in column "D" with header "YTD" the column i want to create in apower query, TABLE-A would be the table in data sheet called "Data" and TABLE-B on data sheet "Calendar", I also added a secondary column in column "E" with header "MTD", that would be another column to create on the query.

As for the other question, there can be as long as 800k rows * 20 columns.

WeTransfer link: https://we.tl/t-HXAs46kbHn

thanks for your help.

Ricardo