Adding a column from the source file name – The file is sourced via a URL

[DataKimble]

Hey all,

I am currently grabbing a CSV file via a public URL:

e.g. Source = Csv.Document(Web.Contents("https://fixturedownload.com/” & “download/" & "target_file" & ".csv"),[Delimiter=",", Columns=7, Encoding=1252…..])

I would like to add a column that references the URL text. Please note that I am not looking to return a live URL in the column. I am just looking to extract the file name, as a string, from the URL.

I am across adding file names from a folder path but am struggling with a URL path. Any help is very much appreciated.

Thanks!

KM

[DataKimble]

Hi John,

Thanks again for your help.

In regards to the original location from which I grabbed the URLs, I have just got them from the website itself. Ideally there would be a contents list of target files and locations available but of course this is not the set up. Instead the URLs are located within different pages on the website.

To some degree though, I think I have been coming at my issue from the wrong direction and therefore over complicating things. I am simply looking to grab a number of years (each year has a different file) of data, tag them by their respective year (i.e. column for Year) and then append them into a single table.  In doing this I was trying to view the website as a folder structure, with designated folders and contents such as files. This is obviously not correct. 

As such I have changed my approach, now focusing on the URLs by implementing them using parameters which allows me to update the target URL as needs whilst at the same time passing the parameter value to the table within a column. 

So I think I am good for now. I again say thanks and apologies for giving you a runaround!

KM

[jbwtp]

Hi @DataKimble,

I think that you should be able just to add a column, something like this:

Table.AddColumn(Source, "URL", each "https://fixturedownload.com/" & "download/" & "target_file" & ".csv")

Is tihs what you are after?

Cheers,

John

[DataKimble]

Hey John, 

Thanks for your reply.  Unfortunatley this is not so much the solution that I am hoping for. 

Your suggested approach looks to involve typing the URL in as opposed to referencing the URL used when souring the data.

To provide more context, I am actually grabbing a number of files from that URL, all of which have the same URL but finishing with a different file names e.g. :

1. "https://fixturedownload.com/download/target_file_2022",
2. "https://fixturedownload.com/download/target_file_2021".

Ideally I would like to source the file name from the URL for each file that I grab. 

Thanks!

KM

[jbwtp]

Hi @DataKimble,

But I guess the URLs + filenames are comming from somewhere? You should be able to grab them from this source. I think this would be easier if you could share a part of your query which deals with getting the files from the URL (including the bits where you set which URL/filenames to be used). From there I will be able to tell you what exactly needs ot be done in your code.

Kind regards,

John