CODEWITHSUNDEEP
excelpowerquery
Gerd
Gerd

Reputation: 138

Join a path variable to each row in the import csv file

I have many Import files, which look like this enter image description here

So there are sales values per Team Member, but NO period inside.

The period is coded in the Path like:

AllData\201501\Revenues.txt
AllData\201502\Revenues.txt
AllData\201503\Revenues.txt

I want to have the Periode from the path on each data row, so my final output table should look like this:

enter image description here

So I must bring the period from the path inside the file anyway.

The question how to access the path is solved in perfect example here:

How can I save a path criteria when I import from folders?

But there I have still the period on the "whole" text, not on the row.

Upvotes: 0

Views: 190

Answers (2)

Alejandro Lopez-Lago - MSFT
Alejandro Lopez-Lago - MSFT

Reputation: 4134

In the linked question you can change the custom column formula from:

Text.FromBinary([Content])

to

Text.Split(Text.FromBinary([Content]), "#(000a)") (depending on how line breaks are represented, you may need to use "#(000a)#(000d)" instead).

This will split the text at each new line, and you'll get a list of the name;value pairs. Click on the box with the two arrows next to the column name to expand the column. Each row should now have the period associated with the name;value pair. Finally, split the column by delimiter on the semicolon to separate the name from the value.

Upvotes: 1

SeanC
SeanC

Reputation: 15923

There are 2 options, both involve horrible looking equations.
First option, we assume the paths are going to have the period in the same position in the string.
for the example, we want the number between the 1st and 2nd slashes.

=TRIM(LEFT(SUBSTITUTE(MID(A1,FIND("|",SUBSTITUTE(A1,"\","|",1))+1,LEN(A1)),"\",REPT(" ",LEN(A1))),LEN(A1)))

If it's between a different set of slashes, alter the ,1 to tell the formula which slash to start from. If the number of slashes can be different, then we will have to try for the second option.

Second option, we assume that those are the only numbers in the path.
This formula will extract those numbers:

=SUMPRODUCT(MID(0&A1,LARGE(INDEX(ISNUMBER(--MID(A1,ROW($1:$25),1))* ROW($1:$25),0),ROW($1:$25))+1,1)*10^ROW($1:$25)/10)

Note that this will extract all the numbers from the string. If the path contains numbers, then these will get added to the string. e.g. C:\2014Data\201401\Revenues.txt would return 2014201401
If this doesn't take care of it, then it may be easier putting a column into the table yourself

Upvotes: 0

Related Questions