How to COMBINE DATA with MERGE and APPEND in Power BI

Подписаться 239 тыс.

Просмотров 124 тыс.

50% 1

Learn how to use merge and append to combine data sets in Power BI.
Download file here datatraining.i...
--------------------------------
📊 TRAININGS 📊
---------------------------------
Power BI Design 4 Weeks Transformation Program my.datatrainin...
Power BI Essentials datatraining.i...
Business User Training datatraining.i...
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.ne...
Newsletter datatraining.i...
---------------------------------
👇 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.epidemicso...
🚀 For growing on RU-vid: www.tubebuddy....
🏄 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

Опубликовано:

16 окт 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 104

@CanonCrested 4 года назад

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 !

@paulstahl3162 4 года назад

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.

@HowtoPowerBI 4 года назад

thank you so much, that's really nice to hear! :)

@SandeepYadav-s5b7l 10 месяцев назад

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.

@CJ-jc8tn 4 года назад

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.

@HowtoPowerBI 4 года назад

Definitely more coming ! Any ideas how to make the numbers soar :D ?

@ameerbux78666 2 года назад

solved all my problems in the first 3 minutes, thank you!

@HowtoPowerBI 2 года назад

Oh wow! Awesome 😎

@MakesSens 10 месяцев назад

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.

@siddharthtyagi1254 2 года назад

one of the best tutorials of power bi i have ever watched.

@vhanessamurillo8473 5 месяцев назад

I can only second this! The best tutorial I've found. Thanks for creating real content and not making people to loose time

@jim5400 10 месяцев назад

Oh my I've been trying to work this out for hours- this was so much clearer thank you!!!!!!

@shalinimenon6248 2 года назад

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

@shalinimenon6248 2 года назад

Pls confirm do we have possibility in power query to store history of data.

@JUHILLAPSIWALA007 Год назад

Great video, I want to know all the option given in merging data and you clear my all doubts Thanks 😊 👌🏻

@willianrocha5475 11 месяцев назад

hi ! awesome video! the bonus content helped me a LOT. It's some daily problems that other videos doesn't show. thanks a lot :)

@HowtoPowerBI 11 месяцев назад

Glad it helped! 😊😎👊

@MAG.experiences 3 месяца назад

Very comprehensive explanation! Appreciate your efforts!

@GTSSOPs 4 месяца назад

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

@jamaluddin4308 4 месяца назад

You are an Excellent Teacher !!!!

@kakashihaa314 10 месяцев назад

amazing content, i find your video yesterday and browse your channel , you helping a lot sir thank you so much

@nomad7317 5 месяцев назад

Man, this was a life-saver!!Thanks a ton!

@Graceadekanmi 4 месяца назад

Great Work!! You make power Bi so easy, love your explanation

@PhillipLearnTeach Год назад

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?

@osPA78 Год назад

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

@jhewitthunt Год назад

Very well explained (again). Any chance of creating a video that shows how to append powerbi datasets to each other please?

@ЭдмондГаллей 2 года назад

Thanks a lot for merge by mult columns, it's very useful!

@weichyiloh9860 Год назад

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.

@sayi77 2 месяца назад

Perfect explanations.

@enricomelis8193 2 года назад

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

@binbinzhou6938 Год назад

Fantastic video! Thanks a lot!

@alanarodriguez9726 27 дней назад

I need your help please! You re very good. But what i need to do is to put precisely two columns stacked one on top of the other without merging the rows. How can i do that?

@jagex4me Год назад

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?

@piotrkuc8936 2 года назад

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?

@חןזיטוק 2 года назад

That's a great video, very helpful and with the right details! thanks.

@HowtoPowerBI 2 года назад

Thanks! Glad to hear this!

@jameslennie90 Год назад

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?

@ahmedalkherbi 2 года назад

Awesome and useful tutorial. Thanks! You always have great content.

@gggrrr8390 3 года назад

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!

@HowtoPowerBI 3 года назад

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 (ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-drY0VRoshFM.html) or you can use powerapps

@gggrrr8390 3 года назад

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

@saitenspieler3489 Год назад

Great explanation, very helpful! Thanks!

@JohnsonAdeyanju-ww5xi 5 месяцев назад

This video really helped

@bennyhermanto3253 3 года назад

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

@ahv5457 Год назад

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.

@Chazzygeoff 2 года назад

You’re awesome. Really clear explanations. Thank you!

@HowtoPowerBI 2 года назад

Thanks Matt :)

@tejasb4427 4 месяца назад

Amazing video❤!

@barttitulaerexcelbart9400 4 года назад

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

@HowtoPowerBI 4 года назад

great point, wished I would have included it in this one :)

@adedamoladurotoye7386 Год назад

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

@AleOrilio Год назад

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?

@norielmag-isa4653 3 года назад

This video is really informative! Thank you!

@qlos23 2 года назад

Do I have to keep the transform folders that are created when imported files or can I delete them?

@unnikrishnansanthosh 2 года назад

Thanks a lot BAS, clear explanations

@sherellet79 Год назад

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.

@90s_HipHop 4 года назад

Great video - great beard!! Subbed

@HowtoPowerBI 4 года назад

Thank you!

@MAG.experiences 3 месяца назад

After appending queries and creating new one as a result, if we disable load, how can we then ensure that it gets the latest data?

@vishnurao2901 4 года назад

Great information..please make a video about DAX functions..

@HowtoPowerBI 4 года назад

if you are starting of with DAX then our new video might be interesting for you ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-pEgOdBkWv4k.html

@kebincui Год назад

Super video. Thanks Bas

@rajasekar8318 7 месяцев назад

hey buddy, i need assistance in power bi query, i'm facing a issue when upload the table in the existing report

@rahulrd2713 2 года назад

you explain it well. thank you

@HowtoPowerBI 2 года назад

Thanks Rahul!

@AkhilDuvvuru 8 месяцев назад

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

@IgnacioLarreaElorz 7 месяцев назад

gracias maestro

@akshayshirbhate7 2 года назад

What if there is 2 tables...one in portrait form n another in landscape.. also having no similar column or rows?

@vedikajalamkar4798 9 месяцев назад

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?

@MohamedIbrahim-et4hq 2 года назад

Great Video

@Soulenergy31 3 года назад

Bas is the Boss!!

@HowtoPowerBI 3 года назад

;) this made my day, thanks!

@mrjuanplacco 2 года назад

Great again!

@HowtoPowerBI 2 года назад

Thanks!

@BlessedAdebomi Год назад

Thanks

@suankitsharma4987 2 года назад

Great video :D

@HowtoPowerBI 2 года назад

Thx Suankit!

@GrinGroomGrow 2 года назад

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?

@mohammedaltaf4877 3 года назад

Excellent Job

@HowtoPowerBI 3 года назад

Many many thanks

@dukestrikcani2925 Месяц назад

When I went to home I could not find the option to append quires. Would anyone know why?

@rafeehelmy4405 2 года назад

U r awesome , thank u

@fdrodreeguez20 3 месяца назад

not to hate on the vide. Because I appreciate you explaining stuff....but note to self....I would rather you just show the window in full screen, than a smaller window and be showing mostly yourself on the video. I think it helps to see the full screen. But at the end of the day this is your video. Just a suggestion on my part. But again, thankful for the information being taught.

@hajeraabdullah9157 2 года назад

your voice is somewhat mesmerising, also great beard

@HowtoPowerBI 2 года назад

Haha thanks a lot Hajera :)

@akshitakhatri4528 Год назад

Hello everyone , I have a question , can we merge two pages into a single page in power bi? Any help will be appreciated 🙂

@pahadianalyst Год назад

Amazing

@kingthranduil8807 3 года назад

When I merge tables, do they have to be related?

@HowtoPowerBI 3 года назад

you need one or more columns on which the data can be matched

@kingthranduil8807 3 года назад

@@HowtoPowerBI thanks!

@LifeofSheru 11 месяцев назад

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.

@Bookcollector07 5 месяцев назад

That usually means that the name on the tab is not corresponding with your predefined tab name.

@matheusguifer Год назад

Can you enable auto captions please? It's essential for the hard of hearing. Thanks!

@an33249 11 месяцев назад

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

@HowtoPowerBI 11 месяцев назад

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.

@panic_seller Год назад

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??

@HowtoPowerBI Год назад

headers of the tables need to be the same

@panic_seller Год назад

@@HowtoPowerBI thanks a lot, i renamed the columns and it worked