Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
Scaffnull
Helper I
Helper I

Combining two columns based on numbers

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.

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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!

View solution in original post

6 REPLIES 6
Scaffnull
Helper I
Helper I

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

Anonymous
Not applicable

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
Super User
Super User

@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.



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

@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!

 


Sheet1Sheet1Sheet2Sheet2

@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!

 


Sheet1Sheet1Sheet2Sheet2

@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.

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.