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

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?

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

Hy, 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

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

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!!!!!!!!!!!!!!!

Hi Lea,

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

https://www.myonlinetraininghub.com/power-query-get-files-from-a-folder

I think it cover it all.

Riny

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!!!

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

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?

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.

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!!!!

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.

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...