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.
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.
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 !
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
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
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.
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.
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?
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
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?
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.
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 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!!!
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 (ruclips.net/video/drY0VRoshFM/видео.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.
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....
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?
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.
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!!
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?
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.
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?
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?
is on the right side of your screen on the tab u'll see queries with a drop down list append queries, merge queries then u click on append queries(append new queries)
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.
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.
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??
To be honest you are a life saver on this video ....Because when my instructor was explaining I was lost and I find it hard to understand.
solved all my problems in the first 3 minutes, thank you!
Oh wow! Awesome 😎
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.
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.
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
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 !
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
Oh my I've been trying to work this out for hours- this was so much clearer thank you!!!!!!
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 video, I want to know all the option given in merging data and you clear my all doubts Thanks 😊 👌🏻
Very comprehensive explanation! Appreciate your efforts!
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 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! :)
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! 😊😎👊
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.
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?
You are an Excellent Teacher !!!!
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
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
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?
Thanks a lot for merge by mult columns, it's very useful!
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 ?
pls what if we have just 2 tables, should we select 2 tables or 3 or more tables ?
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?
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?
Great Work!! You make power Bi so easy, love your explanation
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!!!
Man, this was a life-saver!!Thanks a ton!
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 (ruclips.net/video/drY0VRoshFM/видео.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.
Perfect explanations.
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 :)
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?
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?
Do I have to keep the transform folders that are created when imported files or can I delete them?
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.
Great explanation, very helpful! Thanks!
You’re awesome. Really clear explanations. Thank you!
Thanks Matt :)
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!!
Fantastic video! Thanks a lot!
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?
hey buddy, i need assistance in power bi query, i'm facing a issue when upload the table in the existing report
This video really helped
Awesome and useful tutorial. Thanks! You always have great content.
That's a great video, very helpful and with the right details! thanks.
Thanks! Glad to hear this!
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 ruclips.net/video/pEgOdBkWv4k/видео.html
Thanks a lot BAS, clear explanations
you explain it well. thank you
Thanks Rahul!
This video is really informative! Thank you!
Amazing video❤!
Super video. Thanks Bas
What if there is 2 tables...one in portrait form n another in landscape.. also having no similar column or rows?
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.
You rock!
Can you enable auto captions please? It's essential for the hard of hearing. Thanks!
Thanks
gracias maestro
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?
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?
Hello everyone , I have a question , can we merge two pages into a single page in power bi? Any help will be appreciated 🙂
your voice is somewhat mesmerising, also great beard
Haha thanks a lot Hajera :)
Great Video
Great video - great beard!! Subbed
Thank you!
Bas is the Boss!!
;) this made my day, thanks!
Excellent Job
Many many thanks
Great again!
Thanks!
U r awesome , thank u
When I went to home I could not find the option to append quires. Would anyone know why?
is on the right side of your screen on the tab u'll see queries with a drop down list append queries, merge queries then u click on append queries(append new queries)
Great video :D
Thx Suankit!
Amazing
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.
#3 Appending Details - Different Columns: so then how do we merge both name and Fullname together without correcting the column 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!
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.
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.