combining data of different sorts with no one to one match

[irith]

Hello,
I am new to Power query, and am wondering whether I can do the following:
I have a large data set with data of bird observations on various dates along 30 years. Each line represnts one observation. The columns represnt various data on that bird. Ofcourse, I get multiple observatios on each date (while some dates may be missing).
I have another table with whether data organized by date, one line for each date.
I now wish to add whether variables to the first table, by date. However, as I said, there is no one to one match.

Is there a simple way to do that?

Thank you

 

[Matt Allington]

I suggest using power pivot or power bi. The general pattern is to load both of your tables to power bi. Then load a third table (the calendar) that contains all the dates (from the first date through the last date - make sure you don't miss any dates). For 30 years you will have 10,000+ dates in this calendar table. Read about calendars here Power Pivot Calendar Tables -

Then you use the calendar table as the filtering tool in your report, and this table will filter both of your data tables.

This is a really interesting scenario and I would be happy to help you if you need it and if you care to share the data.

 

[ImkeF]

If I understood your request right, this should easily be doable in Power Query. Load both tables to PQ and then perform a merge from your observations table on the date field of both tables:

= Table.NestedJoin("PreviousStep",{"Date"},Wheather,{"Date"},"NewColumn",JoinKind.LeftOuter)

The last element determines the JoinKind and is key to success here: In this case it keeps all entries from your first table (from which you started) and add only rows from the wheather table where there is a match.
Last step will be to expand all the fields needed from your wheather table.

 

[cmcreynolds]

I'll say it, first: It's funny that you use "NESTED" Join for this data on bird watching....I'm done, continue.

 

[irith]

Thank you Matt and Imke for you replies!!! I did eventually use the Power Query and it worked. It was a great relief to find out that this task was possible (people told me it was not if tables did not have a common non-repeated id in both tables.