Log In
Register
Home
March 10, 2016
Offline
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??
See Answer
Trusted Members
Moderators
January 31, 2022
Offline
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
Answers Post
March 10, 2016
Offline
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?
Trusted Members
Moderators
January 31, 2022
Offline
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
March 10, 2016
Offline
H
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
Trusted Members
Moderators
January 31, 2022
Offline
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?
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
March 10, 2016
Offline
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!!!!!!!!!!!!!!!
Trusted Members
Moderators
January 31, 2022
Offline
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
March 10, 2016
Offline
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!!!
Trusted Members
Moderators
January 31, 2022
Offline
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
March 10, 2016
Offline
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?
Trusted Members
Moderators
January 31, 2022
Offline
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.
March 10, 2016
Offline
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!!!!
Trusted Members
Moderators
January 31, 2022
Offline
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.
March 10, 2016
Offline
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...
Trusted Members
Moderators
January 31, 2022
Offline
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.
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.
March 10, 2016
Offline
Transform Sample For me it looks like this -
= Excel.Workbook(Parameter1, null , true)
See a screenshot
Your Freehold doesn't come like that?
We are working on the exact same files
So what could be the problem?
Trusted Members
Moderators
January 31, 2022
Offline
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:
Edit: Uploaded the wrong screenshot. Above is the correct one.
March 10, 2016
Offline
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?
Trusted Members
Moderators
January 31, 2022
Offline
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.
1 Guest(s)
