• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Problem merging tables from folders|Power Query|Excel Forum|My Online Training Hub

You are here: Home / Problem merging tables from folders|Power Query|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumPower QueryProblem merging tables from folders
sp_PrintTopic sp_TopicIcon
Problem merging tables from folders
Avatar
lea cohen
Member
Members
Level 0
Forum Posts: 219
Member Since:
March 10, 2016
sp_UserOfflineSmall Offline
1
September 4, 2022 - 11:00 pm
sp_Permalink sp_Print
Hi,
I placed 2 files in a folder, combined them into one central file
But the Problem column is missing data - it doesn't pull all the information from the tables why??
sp_AnswersTopicSeeAnswer See Answer
Avatar
Riny van Eekelen
Örnsköldsvik, Sweden
Moderator
Members


Trusted Members

Moderators

Power BI
Level 0
Forum Posts: 440
Member Since:
January 31, 2022
sp_UserOfflineSmall Offline
2
September 5, 2022 - 12:16 am
sp_Permalink sp_Print

Hi Lea,

At first it was a bit difficult to follow your intentions with query steps in Hebrew, but it seemed quite straight-forward in the end. I assume you want to combine the two files, from a folder and include two columns (Name and Date) derived from the file names.

The attached file does just that. Obviously, you need to change the folder location in the source step to one on your own system.

Riny

sp_AnswersTopicAnswer
Answers Post
Avatar
lea cohen
Member
Members
Level 0
Forum Posts: 219
Member Since:
March 10, 2016
sp_UserOfflineSmall Offline
3
September 6, 2022 - 1:55 am
sp_Permalink sp_Print sp_EditHistory
Hi, thanks for the detailed answer!
I worked on the file today and would love to understand -
I did get data - from a folder - in the Content column
 I clicked the button to combine files and only then did I extract the name and date.
Then many numbers in the Account disappeared
And in your way the numbers did not disappear

Why? What is wrong with the way I did?
I just couldn't quite understand the way you did it...
 Thanks for the explanation!!! Thank you very much!

In addition to the command - Extracted Text After Delimiter
There is a command - the number of separators to skip
What does this command mean? What is the difference between 0 and 1?
 
 
Avatar
Riny van Eekelen
Örnsköldsvik, Sweden
Moderator
Members


Trusted Members

Moderators

Power BI
Level 0
Forum Posts: 440
Member Since:
January 31, 2022
sp_UserOfflineSmall Offline
4
September 6, 2022 - 2:25 am
sp_Permalink sp_Print

As said, I had a hard time following what you did, but understood the end result. So, I did it "my own way". Difficult to explain why. It's diner time now in my part of the world and can't work on this tonight. Will get back tomorrow morning (my time).

R

Avatar
lea cohen
Member
Members
Level 0
Forum Posts: 219
Member Since:
March 10, 2016
sp_UserOfflineSmall Offline
5
September 6, 2022 - 11:04 am
sp_Permalink sp_Print
Hfx.jpgImage Enlarger
y, answer at a time that suits you... Another question, I started running the steps But at the stage of adding a function, I cannot click on the next part as described in the picture Thank you for answering
sp_PlupAttachments Attachments
  • sp_PlupImage fx.jpg (36 KB)
Avatar
Riny van Eekelen
Örnsköldsvik, Sweden
Moderator
Members


Trusted Members

Moderators

Power BI
Level 0
Forum Posts: 440
Member Since:
January 31, 2022
sp_UserOfflineSmall Offline
6
September 6, 2022 - 4:58 pm
sp_Permalink sp_Print sp_EditHistory

Hi again,

I don't recognize the dialogue box you showed in the picture, but perhaps I can clarify a bit regarding the solution I proposed. And now that I look at my file again, I realize I had one step too much in the beginning that forced me to insert an extra step before expanding.

Just do the steps Get Data, From file, From Folder and filter out any file you don't want the include. Now press the two arrows in the Content column. PQ takes over and filters out any hidden files, creates a function and invokes it, does some renaming, removes columns and finally expands the column Transform File that contains nested tables. It creates a table with the Source.Name in the first column (like in the screenshot). And on the left you see all the Helper queries that PQ automatically generated. I don't  see these, by the way, in your original file. Did you remove them?

leaPQ.pngImage Enlarger

From here you can follow my steps where I extract text from columns (before or after a delimiter), rather than splitting columns and removing the ones you don't want . But that's my personal preference when the data looks like yours.

Let me know if you get stuck.

Riny

sp_PlupAttachments Attachments
  • sp_PlupImage leaPQ.png (142 KB)
Avatar
lea cohen
Member
Members
Level 0
Forum Posts: 219
Member Since:
March 10, 2016
sp_UserOfflineSmall Offline
7
September 9, 2022 - 12:17 am
sp_Permalink sp_Print sp_EditHistory

I'm trying to do what you describe here mostly stuck at the beginning
- of extracting from the tables.rn(I succeeded in the second step of extracting the text)
My main problem with extracting information from Leiot - the statement of the account did not arrive
Is it because of the original structure of the files?
Is there a way to get a video demonstration of the first part up to the stage of extracting text?
It will help me a lot!!!r
I just don't get along with the written explanation, thank you very much!!!!!!!!!!!!!!!

Avatar
Riny van Eekelen
Örnsköldsvik, Sweden
Moderator
Members


Trusted Members

Moderators

Power BI
Level 0
Forum Posts: 440
Member Since:
January 31, 2022
sp_UserOfflineSmall Offline
8
September 9, 2022 - 1:23 am
sp_Permalink sp_Print

Hi Lea,

I'm not really used to making videos, but Mynda is. Did you check out this video?

https://www.myonlinetraininghu.....m-a-folder

I think it cover it all.

Riny

Avatar
lea cohen
Member
Members
Level 0
Forum Posts: 219
Member Since:
March 10, 2016
sp_UserOfflineSmall Offline
9
September 9, 2022 - 1:40 am
sp_Permalink sp_Print sp_EditHistory
I performed the consolidation of the files exactly as I learned in the Power Query course I purchased from Minda.
I also did the same in the files - but the problem is that there is a lot of data in the account column that is not accepted.
And in your process it did manage to pull the information.


I'm an adder demonstrating the problem in my material video - why can't I add a custom function column?
https://bit.ly/3TYGx2K

I performed step 6 according to the steps you wrote and I receive an error message
Here is a video showing 2 ways I did -
https://bit.ly/3cYMAUD

I really appreciate your patience with all my questions!!!
Avatar
Riny van Eekelen
Örnsköldsvik, Sweden
Moderator
Members


Trusted Members

Moderators

Power BI
Level 0
Forum Posts: 440
Member Since:
January 31, 2022
sp_UserOfflineSmall Offline
10
September 9, 2022 - 3:49 pm
sp_Permalink sp_Print

Hi Lea,

I looked at both videos. Forget about the first one. You don't need to "Remove Other Columns" before combining the Content column. I did the same at first but that wasn't necessary.

The first part of the second video looks OK up to where you show the null values in the second column for "Medical Campus". Have you checked what's in that particular file in the second column? What happens if you connect to that file only? Does it show anything in the second column?

Riny

Avatar
lea cohen
Member
Members
Level 0
Forum Posts: 219
Member Since:
March 10, 2016
sp_UserOfflineSmall Offline
11
September 9, 2022 - 4:03 pm
sp_Permalink sp_Print
Thanks!!!!!
In the original files - all in the same tabular structure and with data in all rows.

Also in the example you prepared in message 2 - the prepared file comes with all the data -
So what did you do in the steps in message 2 - which affects the account so that the data does not disappear?
Why isn't it enough to click on the Content arrows?
What do you gain from adding a column with a custom function?
Avatar
Riny van Eekelen
Örnsköldsvik, Sweden
Moderator
Members


Trusted Members

Moderators

Power BI
Level 0
Forum Posts: 440
Member Since:
January 31, 2022
sp_UserOfflineSmall Offline
12
September 9, 2022 - 5:50 pm
sp_Permalink sp_Print

Let's start over and forget my message number 2. In the beginning of message 6, I tried to explain that I had made a mistake. But I guess I wasn't clear enough. Sorry!

The attached file does just what you described and I should have attached it earlier. Connect to the Folder and click the Content arrows. No removal of columns needed and no need for an extra custom column.

Avatar
lea cohen
Member
Members
Level 0
Forum Posts: 219
Member Since:
March 10, 2016
sp_UserOfflineSmall Offline
13
September 9, 2022 - 5:57 pm
sp_Permalink sp_Print
Additional tables in the same tabular structure are attached.
If you could attach them to your folder and send me the file again,
 simply in one of the steps I encounter an error message...
Thank you very much!!!!
Avatar
Riny van Eekelen
Örnsköldsvik, Sweden
Moderator
Members


Trusted Members

Moderators

Power BI
Level 0
Forum Posts: 440
Member Since:
January 31, 2022
sp_UserOfflineSmall Offline
14
September 9, 2022 - 6:32 pm
sp_Permalink sp_Print

See attached.

The problem was caused by the hyphens in two of the file names. That messed up the extraction process. Just replace the "-" in Medical-Campus and Waterstone-Properties with a space. Now it loads neatly into a 364 row table. Everything seems to be in the right place.

Avatar
lea cohen
Member
Members
Level 0
Forum Posts: 219
Member Since:
March 10, 2016
sp_UserOfflineSmall Offline
15
September 9, 2022 - 6:58 pm
sp_Permalink sp_Print
Look how the files are with me. There is no hyphen between the names.
I load from a folder - click on the Content arrows - and then the table arrives with missing rows
A video is attached - https://bit.ly/3eEfx8L
What process did you do that brings all the rows? 
What processes have you performed now?

I hope you still have patience with me - I just still have a problem here...
 and this is very important - because I need to connect 50 files every quarter...
 
 
sp_PlupAttachments Attachments
  • sp_PlupImage קבצים.jpg (27 KB)
Avatar
Riny van Eekelen
Örnsköldsvik, Sweden
Moderator
Members


Trusted Members

Moderators

Power BI
Level 0
Forum Posts: 440
Member Since:
January 31, 2022
sp_UserOfflineSmall Offline
16
September 9, 2022 - 7:33 pm
sp_Permalink sp_Print

Hard to tell. The files you uploaded definitely have hyphens. Can only wonder why there aren't there on your system. 

But I see another problem now. 25 seconds into the video the first column in the preview is named "Freehold". So, the sample file has its first row promoted to headers. It shouldn't.

Screenshot-2022-09-09-111751.pngImage Enlarger

Look at the applied steps in the Transform Sample File. The Source should look like this:

= Excel.Workbook(Parameter1, null , true)

 

I suspect you have this:

= Excel.Workbook(Parameter1, true, true)

When I change my file to this, I get the same errors as you.

sp_PlupAttachments Attachments
  • sp_PlupImage Screenshot-2022-09-09-111751.png (41 KB)
Avatar
lea cohen
Member
Members
Level 0
Forum Posts: 219
Member Since:
March 10, 2016
sp_UserOfflineSmall Offline
17
September 9, 2022 - 7:54 pm
sp_Permalink sp_Print

Transform Sample For me it looks like this -

= Excel.Workbook(Parameter1, null , true)

See a screenshot
null.jpgImage Enlarger
Your Freehold doesn't come like that?

We are working on the exact same files
So what could be the problem?
sp_PlupAttachments Attachments
  • sp_PlupImage null.jpg (24 KB)
Avatar
Riny van Eekelen
Örnsköldsvik, Sweden
Moderator
Members


Trusted Members

Moderators

Power BI
Level 0
Forum Posts: 440
Member Since:
January 31, 2022
sp_UserOfflineSmall Offline
18
September 9, 2022 - 8:01 pm
sp_Permalink sp_Print sp_EditHistory

Aha! Remove that Promote header step. Don't know whare that comes from.

In the Advanced Editor the code of the Sample File should look like this:

Screenshot-2022-09-09-120806.pngImage Enlarger

Edit: Uploaded the wrong screenshot. Above is the correct one.

sp_PlupAttachments Attachments
  • sp_PlupImage Screenshot-2022-09-09-115953.png (15 KB)
  • sp_PlupImage Screenshot-2022-09-09-120806.png (19 KB)
Avatar
lea cohen
Member
Members
Level 0
Forum Posts: 219
Member Since:
March 10, 2016
sp_UserOfflineSmall Offline
19
September 9, 2022 - 10:10 pm
sp_Permalink sp_Print
Indeed in the Transform File I pasted the code you described in the picture and everything worked out!!!!
I wonder why this happened...
Thank you very much for all your patience and professional help at the highest level!!!
You saved me a lot of time!!

In addition to the command - Extracted Text After Delimiter
There is a command - the number of separators to be skipped
What does this command mean? What is the difference between 0 and 1?
 
Avatar
Riny van Eekelen
Örnsköldsvik, Sweden
Moderator
Members


Trusted Members

Moderators

Power BI
Level 0
Forum Posts: 440
Member Since:
January 31, 2022
sp_UserOfflineSmall Offline
20
September 9, 2022 - 10:32 pm
sp_Permalink sp_Print

Remember the PQ parameters are zero-indexed. So, a zero will extract text after the first occurrence of the "-", a 1 will take the second, a 2 will take the third and so on.

Glad that I could help sort the problem.

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Jack Brett, Martyn Cheney, Rocco Pinneri, LAFONSO HERNANDEZ
Guest(s) 9
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 870
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
A.Maurizio: 202
Jessica Stewart: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
LAFONSO HERNANDEZ
Hayden Hao
Angela chen
Sean Moore
John Chisholm
vexokeb sdfg
John Jack
Malcolm Toy
Ray-Yu Yang
George Shihadeh
Forum Stats:
Groups: 3
Forums: 24
Topics: 6211
Posts: 27239

 

Member Stats:
Guest Posters: 49
Members: 31891
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.