Add the Source from a Web Page to the Table Created

[NeilHI]

Hello,

I've spent most of today searching this topic which gets me nowhere.  

I have selected "Get data", "Web", and added a URL that allows me to select two tables that I want to extract.  That piece works well, but I want add the URL as a column in each of the tables so that I can link them together.  I will then create a separate table with the URL paths and want to loop through them, appending the data in each of the two tables.  I can't even figure out how to add the URL to the two tables.  Seems like something that should be simple.

Thanks

[NeilHI · Accepted solution]

This link led me down the path I think I need to go.  I need to use the URL that is defined in the "Get data" and create a parameter.  From this, I can then add that parameter as a field.

Solved: How to setting a dynamic URL connection - Microsoft Power BI Community

Regards,

Neil

[RobertPaegelow]

= Text.From(Web.Contents([ColumnName]))

Replace "ColumnName" with the name of the column that contains the HTML string. This should extract the URL from the HTML string and add it as a new column in the table.

If you want to extract specific information from the HTML string, you can use the "Document" functions in Power Query to parse the HTML and extract the desired information. For example, to extract the URL from the HTML string, you can use the following formula:

= Document.FromHtml([ColumnName]).Url

[NeilHI]

Robert,

This isn't working for me.  Let me try to step through what I'm looking for again.  The first step in this process is to "Get data", which I am doing from a web URL.  Let's use the following as an example:

Demographics of China - Wikipedia

When this loads, I select the first table as what I want to import into Power BI.  I then transpose the table and then promote the first row to the headers.  The first step, which is to "Get data" has an applied step in Power Query Editor of 

= Web.BrowserContents("https://en.wikipedia.org/wiki/Demographics_of_China")

It is the information within the quotes that I want to store as a field within the table.  By doing this, if I select more than one table that might be on a page, I have a link to the source for each one and can ultimately link them together.  The HTML on the page does not include the URL that is used in the beginning of the "Get data" process.

I hope this helps.

Thanks,

Neil

[RobertPaegelow]

To add the URL as a column in your tables in Power BI, you can use the "Add Column" tab in the Power Query editor. This tab allows you to create new columns in your tables by applying formulas or transformations to the existing data.

To add the URL as a column in your tables, you can follow these steps:

1. In Power BI, open the dataset that contains the tables you want to modify.

2. In the Power Query editor, select the first table that you want to add the URL column to.

3. In the "Add Column" tab, click the "Custom Column" button. This will open the "Custom Column" dialog box.

4. In the "Custom Column" dialog box, enter a name for the new column in the "Name" field. For example, you could use the name "URL" for the column.

5. In the "Custom Column Formula" field, enter the formula that you want to use to populate the new column with the URL. This formula can use the built-in "Source" function to reference the URL that was used to import the data. For example, you could use the following formula to populate the new column with the URL:

   URL = Source

   6. Click "OK" to create the new column.

   After you have added the URL column to the first table, you can repeat these steps to add the same column to the second table. Once you have added the URL column to both tables, you can use it to link the tables together and append the data in your report.

[NeilHI]

I thought it was that easy but it was not.

My steps with the first table in Power Query Editor were:

• Select Add Column tab,
• Selecdt Custom Column,
• Typed "URL" in New column name,
• Typed "Source" (without the quotes) in Custom column formula,
  • Formula looked like: = Table.AddColumn(#"Changed Type1", "URL", each Source)
• Selected OK.

That results in the entire HTML string being placed in the field and not the URL path, which is what I'm looking for.