Extracting Multiple Tables From A Power Query Combine Folder Source
HTML-код
- Опубликовано: 3 дек 2018
- One of the amazing data connectors in Power Query is the ability to combine files from a folder. However, what happens when those files each contain multiple tables you'd want to combine? This video covers a simple technique to extract multiple tables from each file, using the folder combine data connector.
LET'S CONNECT! 🧑🏽🤝🧑🏽 🌟
-- / havensbi
-- / reidhavens
-- / havensconsulting
HAVENS CONSULTING PAGES 📄
Home Page - www.havensconsulting.net
Blog - www.havensconsulting.net/blog-...
Blog Files - www.havensconsulting.net/blog-...
Files & Templates - www.havensconsulting.net/files...
Consulting Services - www.havensconsulting.net/consu...
Contact & Support - www.havensconsulting.net/conta...
EMAIL US AT 📧
info@havensconsulting.net
#PowerBI #PBI #microsoftpowerbi #HavensConsulting #powerplatform #microsoft #businessintelligence #datascience #office #data #digitaltransformation #dataanalytics #tableau #excel #powerapps #datavisualization #dashboard #sharepoint #python #bi #analytics #cloud #azure #bigdata #sqlserver #software #sql #dynamics #dataanalysis #yammer #microsoftpowerbi #onedrive #machinelearning #bhfyp #powerbidesktop 
Наука
i have been struggling with this for the past two days... i kept making new queries, and it was cluttering up the project with all these "Parameters" i didnt want... i am so glad i found your video! i was pulling my hair out in frustration.
Glad it helped!
Thank you so much, Reid! This is exactly what I am looking for. Always be a fan of your channel.
Thank you. This is exactly what I needed.
Thank you Reid for the video! 👍
Had the opportunity to use this recently because dataflows was not an option.
It immediately eliminate this error - Query references other queries or steps, so it may not directly access a data source.
thanks for this video! this was exactly what i was looking for!
You're welcome! Thanks for watching!
Reid, thanx, perfect! Could copy that one for my current project
Thank You SO MUCH!!! From BRAZIL!
You're welcome! Thanks for watching!
Brilliant Trick!! Reid thanks very much!!
😅🤣😅😅🤣😂😅🤣😂
I feel like an idiot for not trying this!
Many thanks, much appreciated 🙏
Thanks , that what I am looking
No problem 👍
Thanks, was missing the trick of selecting the folder instead of a table!
Glad it helped!
5:03 is there a way to Expand all of columns at once? (in case we have multiple columns with tables on it, like importing xml files)
How would you convert the created Power BI query to an excel. As explained in video. Once you finish creating the Power BI in case if i want to view it in a single excel how do it Thanks!
If you want to use this query from Power BI, into a query in the query editor in Excel. You can right click -> copy in Power BI on the query. Then go to the query list in Excel Power Query, right click -> paste and it'll copy the query over. Also Excel has a folder combine option, you can just start from there.
Great video! Follow-up question however. I have a very similar situation however, my two sets of data I want to capture are actually in the same .csv file / same worksheet instead of two separate tables (within the same worksheet file). In other words I have FileA and I want the first 5 columns to go to USER table, and the next 10 columns to go to PAGE_VIEWS. For FileB, first 5 columns to go to the same USER table, and the next 10 columns to go CLICK_VALUES. For FileC, first 5 columns to go to USER table, and the next 10 columns to go to ASSESSMENT_RESULTS. And so on. Hopefully that makes since. Only difference I see is in your example the data is in two different tables where as mine in in the same. Can the combine feature be used in this scenario? Thanks!
Hi Michael, this might be best solved via an email. If you can provide a file I'd be happy to take a look. info@havensconsulting.net
can I do this for a folder in onedrive for business?
learn.microsoft.com/en-us/power-query/connectors/sharepoint-folder
Absolutely :)
Hello, thanks for a very helpful video. Well done! I would like to ask - would you know why this approach does not work when loading data in a dataflow? I get to "Combine Files" window (2:38) but I am not able to select the folder itself. It can not be selected, therefore I can not process further. Strange thing is that this approach works when normally loading data (not in dataflow). Could you please help? Thanks a lot!
The feature parity between Power Query Online (dataflows) and Power BI desktop isn't always exact. Since they're actually built on different coding platforms. I'll have to look into why it isn't available in Power Query online.
@@HavensConsulting Thank you so much for your quick answer. I have just been thinking... would this approach work? 1) do the job in desktop (get data -> folder -> combine and transform, etc.), 2) copy the M code from desktop, 3) paste this code into dataflow "blank query" source. Honestly I am good at DAX, but M code is a all greek to me so I am not sure if this is supposed to work... ?
@@tomasdolejsi2365 that's actually a solution that should work! You can definitely copy/paste code from desktop to PQ online and it should just work :)
@@HavensConsulting that's great, I am going to definitely test it asap :) I know this might be a little bit more off-topic but how would you approach a situation where your folder contains source files for 2 different reports? So you need to "filter" the files by a file name.
Let's imagine our folder contains:
report1_2020.mdb, report1_2021.mdb and
report2_2020.mdb, report2_2021.mdb?
Report 1 and Report 2 are different reports. Each year a new file is created for each report. The data structure is the same. And you want to make the solution dynamic... (so whenever a new file is created it is automatically added to dataflow as a source). So I am literally talking about "filtering" in a folder by contained strings in a filename. Any thoughts are much appreciated! Thank you.
@@tomasdolejsi2365 in the query before it combines the files you have a list of the files in that folder. The column with the file name in it you can filter to contains or does not contain to focus on the files you want to combine
Thanks.
Is it possible to extract a parameter/function that can be reused on other similar folders named Data1, Data2, etc? Thanks