Combining two columns based on numbers

[Scaffnull]

Hi everyone,

I'm a newbie when it comes to PowerBi but with some background in Excel. I have two tables (Sheet1), (Sheet2) that consist with matching numbers. I'm searching for a function to first go through every row of each column, and if there is a match then add them into a new column. Also, if there isn't a match from (Sheet1) to (Sheet2), add the new numbers from (Sheet1) on to (Sheet2)?

I have a hard time figure out the functions to use, and how do I specify to look for the matching numbers?

Thanks for any help.

[Scaffnull]

@amunshi Perfect I will check it out. Appreciate the help! Thanks

[amunshi · Accepted solution]

You can achieve the automation using Google Sheets and Google Scripts to copy the missing values from Sheet1 to Sheet 2 and then feeding this final sheet as the input to your PowerBI Dashboard. 
Google Sheets can be set on auto-update where if the data in either sheet changes the other will have it copied with the help of triggers available in Google Scripts. 
And there is a video on how to automate data refresh in PowerBI so whenever your dataset is refreshed in PowerBI the same will reflect in your dashboards. Hope this helps.  
https://www.youtube.com/watch?v=-8NlEeIIRiQ

Did I answer your question? Mark my post as a solution!

[Greg_Deckler]

@Scaffnull Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[Syndicate_Admin]

@Greg_Deckler , Thank you for your relpy! Attached is two documents (Sheet1) (Sheet2). 

(Edited documents). But I want to combine the two, and if WorkNr matches in both sheets, I want to add Hours Worked. And if WorkNr (from Sheet1) do not match, I want to add a row and add Hours Worked. I assume the best way would be to compare the two and add a new table with the information? Fairly new to all of this so having a hard time how to set things up, also explain in a correct way. (Will get there)

Thank you for your expert help!

Sheet1Sheet2

[Scaffnull]

@Greg_Deckler , Thank you for your relpy! Attached is two documents (Sheet1) (Sheet2). 

(Edited documents). But I want to combine the two, and if WorkNr matches in both sheets, I want to add Hours Worked. And if WorkNr (from Sheet1) do not match, I want to add a row and add Hours Worked. I assume the best way would be to compare the two and add a new table with the information? Fairly new to all of this so having a hard time how to set things up, also explain in a correct way. (Will get there)

Thank you for your expert help!

Sheet1Sheet2

[Scaffnull]

@Greg_Deckler to metion, now the tables look small and would just be easier to copy and paste. But in reality the document is much bigger, so to figure out an automated way would be ideal.