Тёмный

Best Practices for Combining Data from Multiple Excel Files 

Goodly
Подписаться 92 тыс.
Просмотров 14 тыс.
50% 1

In this video, we will learn some of the best practices to follow while combining data from multiple Excel files.
===== ONLINE COURSES =====
✔️ Master 'M' in Power Query -
goodly.co.in/learn-m-powerquery/
✔️ Mastering DAX in Power BI -
goodly.co.in/learn-dax-powerbi/
✔️ Power Query Course-
goodly.co.in/learn-power-query/
✔️ Master Excel Step-by-Step-
goodly.co.in/learn-excel/
✔️ Business Intelligence Dashboards-
goodly.co.in/learn-excel-dash...
===== LINKS 🔗 =====
Blog 📰 - www.goodly.co.in/blog/
Corporate Training 👨‍🏫 - www.goodly.co.in/training/
Need my help on a Project 💻- www.goodly.co.in/consulting/
Download File ⬇️ - goodly.co.in/best-practices-f...
===== CONTACT 🌐 =====
Twitter - / chandeep2786
LinkedIn - / chandeepchhabra
Email - goodly.wordpress@gmail.com
===== WHO AM I? =====
A lot of people think that my name is Goodly, it's NOT ;)
My name is Chandeep. Goodly is my full-time venture where I share what I learn about Excel and Power BI.
Please browse around, you'll find a ton of interesting videos that I have created :) Cheers!

Наука

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

 

1 авг 2024

Поделиться:

Ссылка:

Скачать:

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

Добавить в:

Мой плейлист
Посмотреть позже
Комментарии : 36   
@jamesjacobslai9377
@jamesjacobslai9377 27 дней назад
For Folder Contents portion, could you create a tutorial on how to do it with Sharepoint Folder Path? Thank you for these tips! Very helpful!
@kuuuyajim
@kuuuyajim Месяц назад
Some of these tips I picked up from different tutorials and some I was able to figure out myself. And indeed, they help improve refresh of the query. 😃
@erzsebetbiro7153
@erzsebetbiro7153 29 дней назад
Dear Chandeep! I suggest, You could also make additional videos on the power query optimization options as discussed in the video. Please give me your suggestion. I have a problem with the notebook I use at my work. It has an old i3 cpu, 8GB of ram and use Office 365. Dozens of (currently 80 files) csv files need to be processed. Their individual file sizes are approx. 800 MB and the number of rows sometimes exceeds 1 million per file before cleaning. The files are distributed in directories. Imagine that there are cities, stores in the cities, products in the stores, and finally sales data for each product in the stores per customers. Should I read the individual files and then combine them, or should I read the main library? Than filter the necessary data types? Should I store the queries in memory or load them into excel pages? What if there are more rows than 1 million? How should I handle large amounts of data? Maybe I should switch to the MS Access program? Or should I upload all csv files to SharePoint and read them from there with PoweQuery?
@chrism9037
@chrism9037 Месяц назад
Thanks Chandeep! Awesome as always
@emilmubarakshin49
@emilmubarakshin49 Месяц назад
These tips are absolutely essential and apply way over importing data from multiple csv/excel files. That’s what any sql, bi, data analyst should know and apply🎉
@ExcelWithChris
@ExcelWithChris Месяц назад
Great one!!! Loved it.
@alexrosen8762
@alexrosen8762 Месяц назад
Good advice on an important topic 👌
@macheraldi
@macheraldi День назад
Hi Goodly, do you have a way to combine xls files?
@apamwamba
@apamwamba Месяц назад
Exellent1!!
@txreal2
@txreal2 Месяц назад
Btw, I believe you can transform a subset of the data (like sample file in the transform step) with power query, then convert these power query applied steps into SQL to apply to the larger dataset.
@MoonShine-bs6cl
@MoonShine-bs6cl Месяц назад
Awesome as alwys🎉 please can you make a video on snowflake database data load to power bi and best practices for handling huge database in power query... Thanks
@afrovs2862
@afrovs2862 29 дней назад
It’s simpler to control formatting with xlsx files than csv - especially with leading 0’s
@fanlovexcel
@fanlovexcel Месяц назад
Nice work Chandeep :)
@karthikeyankms8672
@karthikeyankms8672 29 дней назад
Hi, How to Save All the custom functions from one pbix file as a file and import those custom functions into other pbix files?
@aleprv
@aleprv Месяц назад
Hi! Very nice video. I have one question, what is the best approach to work with excel files that are generated in different languages?
@HachiAdachi
@HachiAdachi Месяц назад
Chandeep, did you say you can do **Incremental Refresh** when files are saved in SharePoint?! 🤯 Can you point to where I can learn how to do that because that would be a game changer for me!! 😁 Thanks!!
@jamesjacobslai9377
@jamesjacobslai9377 Месяц назад
+1!
@kuuuyajim
@kuuuyajim Месяц назад
I think the incremental refresh is only possible with a Premium account.
@shwetkumar1719
@shwetkumar1719 6 дней назад
Can we perform upsert in power query
@hcaseres
@hcaseres 16 дней назад
Hello Goodly, a question. Is it possible to generate a sheet automatically in Power Query? I have a table with a question number column and the rows are the answers. I want to separate the question numbers into sheets. Sheet="1" with all the answers to question 1, sheet="2" with all the answers to question 2... sheet =62 with all the answers to question 62.
@exceldude2001
@exceldude2001 Месяц назад
how about savings excel files in binary - xlsb it help with refreshes
@shubhabratadey
@shubhabratadey Месяц назад
One small question. Even though csv is a better option than excel when it comes to raw data but their file sizes are quite higher than excel. In older Office, I have seen csv s to be quite smaller in sizes when I would convert them from Excel but it doesn't happen nowadays. How can we address this?
@kuuuyajim
@kuuuyajim Месяц назад
I have the same observation with CSV files.
@mrbartuss1
@mrbartuss1 Месяц назад
I have a question. Do you have a video on how to get data from folder without combining the files, but creating separate queries for all of them?
@justapasserby69420
@justapasserby69420 Месяц назад
Create a reference query pointing yo the folder where you have your files and load individually by referencing and filtering
@mrbartuss1
@mrbartuss1 Месяц назад
@@justapasserby69420 I’m looking for a more automatic way to
@jofielalejandrosantaella4058
@jofielalejandrosantaella4058 Месяц назад
Does PQ have some way to look how much space(?) does it take to execute each step?
@GoodlyChandeep
@GoodlyChandeep Месяц назад
You can do that with Query Diagnostics in the Tools Tab in Power Query (Power BI)
@CiachoAcM
@CiachoAcM Месяц назад
How to merge multiple files, not combine? Do you know The solutions to merge more than 2 files/tables?
@dharmmu
@dharmmu Месяц назад
Sorry, but what you meant by merge but not combine?
@CiachoAcM
@CiachoAcM Месяц назад
​@@dharmmuI mean join :) you can combine a lot of files but what about join/merge. If you want to join tables there is only possibility to do it with max 2 tables using specific ID columns. I would like to have one "base table" and join multiple tables (saved in one folder) to them.
@dharmmu
@dharmmu Месяц назад
@@CiachoAcM the other tables are in different sheets or workbook or in one sheet itself you have many different tables. Also, tables are in proper excel table format or it's just a sets of data in excel.
@alterchannel2501
@alterchannel2501 22 дня назад
why isn't Folder.Contents showing in the intellisense? ..... ok solved. the intellisense was not working well at all. I closed excel and opened it again and everything is normal again. Another Excel mystery
@farooqtahir538
@farooqtahir538 Месяц назад
So basically he is saying how to make things unnecessarily complicated
@brianxyz
@brianxyz Месяц назад
Where are you getting that from?
Далее
ЮТУБ ТОЧНО ВСЕ!
11:23
Просмотров 602 тыс.
5 Surprising Power Query Tricks You Need to Know!
14:14
This is how I ACTUALLY analyze data using Excel
24:05
КАКОЙ SAMSUNG КУПИТЬ В 2024 ГОДУ
14:59
Battery  low 🔋 🪫
0:10
Просмотров 13 млн
Telefonu Parçaladım!😱
0:16
Просмотров 30 млн