How to COMBINE DATA with MERGE and APPEND in Power BI
ฝัง
- เผยแพร่เมื่อ 29 มิ.ย. 2020
- Learn how to use merge and append to combine data sets in Power BI.
Download file here datatraining.io/powerbi-how-to
--------------------------------
📊 TRAININGS 📊
---------------------------------
Power BI Design 4 Weeks Transformation Program my.datatraining.io/pages/powe...
Power BI Essentials datatraining.io/powerbilearni...
Business User Training datatraining.io/powerbi-busin...
For Custom Trainings and Consulting email directly support@datatraining.io
---------------------------------
😍 JOIN 😍
----------------------------------
Join bit.ly/4b453bi
Subscribe bit.ly/31MnQGO
Insta / howtopowerbi
LinkedIn / basdohmen
TikTok / how.to.power.bi
X / howtopowerbi
fb / howtopowerbi
Threads www.threads.net/@howtopowerbi
Newsletter datatraining.io/newsletter
---------------------------------
👇 CHECK THIS OUT! 👇
---------------------------------
💻 My gear amzn.to/47F21Yc
📚 Power BI books MUST READ! amzn.to/3tUfFcj
💡 General books I recommend amzn.to/48YNo33
🎶 Music for my videos www.epidemicsound.com/referra...
🚀 For growing on TH-cam: www.tubebuddy.com/bas
🏄 Stuff I use daily amzn.to/3HqfMQ2
* Above are affiliate links, which means at no additional cost to you, if you make a purchase using these links we will receive a small commission. It supports us and helps us to continue making more How to Power BI videos!
Thanks for being a part of this channel and all your support! 💪 🙏
#HowToPowerBI #PowerBI #DataTraining
#powerbidesktop #powerbitraining #powerbideveloper #DAX - วิทยาศาสตร์และเทคโนโลยี
Great video as usual. Love how you always refer to real life practical issues that might occur. I noticed merging my data always lead to long refresh time and I've learnt once I merge it I really should drop it from refreshing, Since it's now in my new table. That would help my refresh time. Thanks a lot !
Oh my I've been trying to work this out for hours- this was so much clearer thank you!!!!!!
Another excellent video. I particularly liked your clear explanations on the various join conditions in Power BI. Looking forward to many more informative tutorials from you in the coming months (and years!) and seeing your subscriber numbers soar.
Definitely more coming ! Any ideas how to make the numbers soar :D ?
I have scene many video on appending but most of them just explained the definition and done, but you showed different scenarios and their outcome. It was really amazing brother just subscribed you immediately.
Thank you for your well thought out videos. Your explanations are thorough and the visuals that you provide really help understand the processes. I appreciate your efforts to create meaningful content for professionals and hobbyists alike.
thank you so much, that's really nice to hear! :)
Hey Bas, starting from the bottom of your catalogue and working my way up. REALLY should have watched this one a few weeks ago, would have saved myself a lot of hassle! Thanks for all the help.
This video was amazing! I am currently working on a project very similar to the examples that you showed. I'll be implementing the steps that you've mentioned. Gracias!!!
solved all my problems in the first 3 minutes, thank you!
Oh wow! Awesome 😎
Great video, I want to know all the option given in merging data and you clear my all doubts Thanks 😊 👌🏻
Man, this was a life-saver!!Thanks a ton!
Thanks a lot for merge by mult columns, it's very useful!
Thanks for this. Also when you count the rows it would help if you show exactly what you click to remove the row count. I just started over with combining the tables since I wasn't able to find how to remove row count
Awesome and useful tutorial. Thanks! You always have great content.
Hi,
Great video, really appreciate your time and effort putting it all together.
Queston: how to join tables, where one row is related to multiple rows in another table?
one of the best tutorials of power bi i have ever watched.
I can only second this! The best tutorial I've found. Thanks for creating real content and not making people to loose time
You are an Excellent Teacher !!!!
amazing content, i find your video yesterday and browse your channel , you helping a lot sir thank you so much
Thanks for the help. One tip, to help freshusers like me give quick explanation of how to save the query, what's the consequence of unload datasets,keep track of all the actions to potentially re-write them, and how to use the outcome for the subsequent visual activities. In my case this would have been more intuitive and practical than explaining how to get subsequent data from a folder. But again great work ;) 5/5
This video is really informative! Thank you!
hi !
awesome video! the bonus content helped me a LOT. It's some daily problems that other videos doesn't show.
thanks a lot :)
Glad it helped! 😊😎👊
Great explanation, very helpful! Thanks!
Great Work!! You make power Bi so easy, love your explanation
You’re awesome. Really clear explanations. Thank you!
Thanks Matt :)
That's a great video, very helpful and with the right details! thanks.
Thanks! Glad to hear this!
Fantastic video! Thanks a lot!
Thanks a lot BAS, clear explanations
Thanks for he video. Should I limit the rows (50k) in every table b4 want to merge before and also on the merged table as well?
Thank you for the great video. I realised if there is some complex part has to be solved, the place to go is your videos, you are awesome.
One query: in Append function it's replacing the original data whenever it gets refreshed. How to append rows one below the other without overwriting old data.
Example: we have last week data in table and when we refresh this week, this week data should be added below the old data instead overwriting. Pls guide
Pls confirm do we have possibility in power query to store history of data.
Great content, could you please make a best practice for refresh power BI if there is any updates on every single file in that folder
Very well explained (again). Any chance of creating a video that shows how to append powerbi datasets to each other please?
Thank you for the video - really great. I have 3 tables for historic and current data that I append together. I have unclicked the enable load of the historic data but when I refresh the big appended table ( historic + current) the historic data refreshes again - even though it never changes. As the historic data contain about 5 million rows this takes a long time. Is there a way to stop this?
This video really helped
These videos are great. I know this is an older one, but hopefully you will see this :)
I use Power BI a lot, but at the moment I'm struggling with something similar:
- ODATA feed from Business Central into Excel. Lots of entries/rows being added, changed, & removed on a weekly basis.
- Offline workers need a manual data entry column to add comments, dates etc...
- When refreshing data, I can't figure out out to 1. Overwrite / update any fields that have changed on BC, 2. Keep the per-row comments/dates linked to the original row, even if some data has changed.
I've tried a couple of ways using 2 queries and trying to reference one with the other, but I either get thousands of duplicates, or when I refresh, the comment columns aren't linked to the new/updated rows and they end up out of line.
I feel like merging / appending would solve this but I can't get it to work yet. Any tips would be much appreciated!
thank you!! You would need to look into ways to write-back to the source. There are some custom visuals that support this like this one here (th-cam.com/video/drY0VRoshFM/w-d-xo.html) or you can use powerapps
@@HowtoPowerBI Hi! Thanks for the reply! The visual in this case is an excel spreadsheet, which displays the query result. Then there are some manual data entry columns added. When refreshed, the lines don't stay in sync, even with an index column. I'm not sure how to link it. Not really Power BI per se, but I thought you might be a good person to ask!
Thanks for the video. I have two identical datasets: one is a historical data and saved as a csv file and the other one is from an API which gets refreshed everyday. I have built all my dashboards based on the API. Now if I follow your steps and append the historical one with the API one, does the data that’s coming from API in the appended table still gets updated or it overwrites the historical one as well? I need to keep the historical data fixed and refresh the API data daily.
Amazing video❤!
Great video: all this information in just 23 minutes. One thing: maybe for another topic. If you have multiple instances in a table and you merge then, you have to be carefully. For example 2 order files of locations A and B. In both files a customer can have ordered multiple times. You first have to group before you merge....
great point, wished I would have included it in this one :)
you explain it well. thank you
Thanks Rahul!
Great content, tyvm for sharing. Question: what if the headers of one file are in Dutch and the other in English? So contentwise it is ok to append but the names are different?
U r awesome , thank u
Great Video
Super video. Thanks Bas
Great video - great beard!! Subbed
Thank you!
Do I have to keep the transform folders that are created when imported files or can I delete them?
Great information..please make a video about DAX functions..
if you are starting of with DAX then our new video might be interesting for you th-cam.com/video/pEgOdBkWv4k/w-d-xo.html
Great again!
Thanks!
Hi, great video i do have a question what if the salary for the employee is updated from a new file in the folder can powerbi update the salary column
Thanks for your sharing, it is useful for me to load excel files in a folder. But I am facing an issue, everytime I refresh, I am required to wait to refresh old files(which no changes) May I know it is possible only refresh new files? Thanks.
gracias maestro
Great video :D
Thx Suankit!
Excellent Job
Many many thanks
Thanks
Amazing
Should you be able to merge two data sources e.g. excel and a BYOD. I have been able to do it in desktop and refresh without issue but as soon as I publish to the web the refresh stops working - data connection issue but no error info of any help.
I have been able to drill down to my merge tables being the issue, as if I disable them, the rest of the report refreshes ok.
Bas is the Boss!!
;) this made my day, thanks!
Your video was SO helpful! Thank you so much!
I just have one question, how do I combine 2 unrelated tables?
For example:
Table 1: Fruits
Apple
Strawberry
Table 2: Vegetables
Kale
Lettuce
Spinach
Desired result:
Fruit,Vegetable
Apple,Kale
Apple,Lettuce
Apple,Spinach
Strawberry,Kale
Strawberry,Lettuce
Strawberry,Spinach
Is It possible? How?
your voice is somewhat mesmerising, also great beard
Haha thanks a lot Hajera :)
What if there is 2 tables...one in portrait form n another in landscape.. also having no similar column or rows?
hey buddy, i need assistance in power bi query, i'm facing a issue when upload the table in the existing report
Hey, can you help me with how to append Table 2 (ID, Name, Sal) into Table 1 (ID, Name, Sal) where filter out the IDs from Table 2 if the same ID is already exist in Table1 using M-Query in Power Query editor. Thanks!!
Can you enable auto captions please? It's essential for the hard of hearing. Thanks!
what happens when while appending the files repeat the data, there is some addition to the data in terms of rows but the rest of the data remains the same. How to deal with that. I will try to explain more about it through an example like data is copied from an ERP system to an excel file & every month that file repeats carry the same data along with the new data entered into the system, then a new file is extracted again to append. How to not carry the other values & carry only the new ones??? Can you please help?
If I have 2 excel sheets one contains 100 records other contains 200 records. No common column between them to create relationships. But I have to perform an addition operation on the unit price column which is present in both but the value contains different . How can I perform an addition and generate a new column ? If merg is used then it will need the same number of records and append i don't want. What is the solution for this? Please tell me the solution?
Hello everyone , I have a question , can we merge two pages into a single page in power bi? Any help will be appreciated 🙂
It is still very confusing, i have a zip file which contains various data like few excel files (which is fine), but a map, and some random pdf and text file which are not in the form of table, I don't know what to do
1. unzip 2. connect to the folder and put a filter on .xlsx 3. combine by clicking expand in the content column header 4. create a new query for the pdf (pbi can read tables from the pdf) 5. Create a third query for the text file.
Hi Can anyone help with the error message (" [Expression, Error] The key didn't match any rows in the table") I am getting while running the power query? Basically, I have a Power Query appending 19 Excel files from a folder but sometimes when I am refreshing the data the files may be in use by users and this error message will pop up.
That usually means that the name on the tab is not corresponding with your predefined tab name.
When I merge tables, do they have to be related?
you need one or more columns on which the data can be matched
@@HowtoPowerBI thanks!
#3 Appending Details - Different Columns: so then how do we merge both name and Fullname together without correcting the column name..
My append adds data horizontally instead of vertically, like I have 2 tables with 3 columns each, it creates another table with 6 columns instead of APPENDING, why??
headers of the tables need to be the same
@@HowtoPowerBI thanks a lot, i renamed the columns and it worked
0:42 - Append
3:37- Merge
Are you Dutch?
Good guess:)
@@HowtoPowerBI Thnks for the good video. It was really usefull.
Awesome and useful tutorial. Thanks! You always have great content.