Forum Discussion
Power-Query Manually Replacing Missing Data within Queried Column
Hello!
I want to start off by saying I've never worked with Excel professionally before, I've only used it for very specific purposes that fit my Interest. So this might have a easy solution or plainly be impossible.
My current interest is Tracking the meta-data of Articles I read online.
Through Power-Query I've found a way to automate this to a point where I can just add the link of the article to a list of URLs and power-query fills out all the meta data, because I've trained it to pull the correct data through the "Table from Examples" function of Power-Query.
Following the instructions of the following Article, I've Changed the code of the Power-Query to accept multiple links instead of one singular one with all the Data.:
https://www.howtoexcel.org/how-to-extract-data-from-multiple-webpages/
The code looks something like this:
let MetaData=(URL) =>
let
Quelle = Web.BrowserContents(URL),
#"Aus HTML-Code extrahierte Tabelle" = Html.Table(Quelle, {{"Title", "H4:nth-last-child(4) > :nth-child(1)"}, {"Author", ".heading:nth-child(1) A + *"}, {"Topic", ".landmark + .tag"}, {"Published", ".Date*"}, {"Publisher", ".category-slash *"}, {"DateAccessed", ".time*"}}, [RowSelector=".blurb"]),
#"Geänderter Typ" = Table.TransformColumnTypes(#"Aus HTML-Code extrahierte Tabelle",{{"Title", type text}, {"Author", type text}, {"Topic", type text}, {"Published", type text}, {"Publisher", type text}, {"DateAcessed", type date}})
in
#"Geänderter Typ1"
in MetaData(It's in German, but Quelle=Source, "Geänderter Typ"="changed Type". I've modified it slightly to hopefully reflect the simulated Data later in this post.)
This Connection is then used to insert a Custom Column and expand it, so the Data appears in the new table.
The Problem I'm now having is that some of the Articles are only accessible with an Account on the page I'm using to read the Articles. This means that Power-Query Returns null for those articles across all columns.
Original Table:
Extracted Data:
(This is manually simulated Data, not actual links to the articles)
What I now want to do, is manually insert the Data for the Article in Row 4.
I've coloured them red in this screenshot to show what I mean.
However, if I refresh the table (For example because I've added more links to the original Table, because I've read more Articles), the red data disappears.
My browsing of Excel Forums has taught me that it's possible to create a self-referencing-query to manually add data to a table, if that data is in it's own, new Column.
However, since my manual data is in the same columns that the power-query fills out, and I lack the fundamental understanding how Power-Query or the programming language of it works, I don't see how this solution can help my case.
I'd love to be proven wrong.
I hope somebody can help.
Thank you in Advance :)
- Yorin
So you want to build a history table and keep the annotations made to previously extracted data. Then I assume that what you call "Original Table" will only include URLs that haven't previously been loaded from the web. Let's call it "tOriginal".
The first time, PQ generates the "Extracted Data". Give that table a meaningful name, for instance "tAnnotated". Enter data wherever needed. You can even over-write existing data. So this would become the table in your last screenshot.
Adjust the M-code so that you create not one Source step but two steps. Let's call them "Source1" and "Source2". Add a step that appends Source1 to Source2. It would look like this.
let
Source1 = Excel.CurrentWorkbook(){[Name="tOriginal"]}[Content],
Source2 = Excel.CurrentWorkbook(){[Name="tAnnotated"]}[Content],
Append = Table.Combine({Source2, Source1})
in
Append......... where Source1, obviously, results from your MetaData function. So, replace the Bold Italic part with whatever creates your "Quelle".
Clear tOriginal and enter some new URLs and refresh the query. The new URL meta data should now be added to the bottom of the previous PQ output table.
The attached file contains a working example. Add some data in the blue table, Refresh and see what happens. Annotate some data in the green table, clear the blue table and type some new data. Refresh!!
6 Replies
Would need to see your file.
Managed to get it to work with your original solution, just had to adjust how I was referencing to the original table, everything works now. Thank you so much!
Difficult to help without having your file. Can you create one where your Quelle step is pointing to an Excel table in the same workbook? Than I can try to work on that one explain better what to do.
Hello! its me again!
I've now worked out the problems I was having with the names of the sources and everything.
The "Appending" Part of the query works now and it attaches the new URLs and cells of the collumns at the bottom of the previously extracted data.
The problem I'm having now it that once Appended, all the newly extracted data gets replaced with NULL values.
If I click through the previous steps, the newly extracted data shows up in the all the steps it's supposed to show up in, but it doesn't in the very last step, the appended table.A quick search told me this happens if the columns are not named the same, however since the code that created both sources is practically the same, all the names for all the columns are spelled the exact same way.
Which is why I don't understand why this is happening.
It's almost as if as soon as I execute the "Append" command, it tries to re-extract the new data, fails, and then appends the null-Data.
Maybe you have an Idea what is happening.
Thank you again for your help!
So you want to build a history table and keep the annotations made to previously extracted data. Then I assume that what you call "Original Table" will only include URLs that haven't previously been loaded from the web. Let's call it "tOriginal".
The first time, PQ generates the "Extracted Data". Give that table a meaningful name, for instance "tAnnotated". Enter data wherever needed. You can even over-write existing data. So this would become the table in your last screenshot.
Adjust the M-code so that you create not one Source step but two steps. Let's call them "Source1" and "Source2". Add a step that appends Source1 to Source2. It would look like this.
let
Source1 = Excel.CurrentWorkbook(){[Name="tOriginal"]}[Content],
Source2 = Excel.CurrentWorkbook(){[Name="tAnnotated"]}[Content],
Append = Table.Combine({Source2, Source1})
in
Append......... where Source1, obviously, results from your MetaData function. So, replace the Bold Italic part with whatever creates your "Quelle".
Clear tOriginal and enter some new URLs and refresh the query. The new URL meta data should now be added to the bottom of the previous PQ output table.
The attached file contains a working example. Add some data in the blue table, Refresh and see what happens. Annotate some data in the green table, clear the blue table and type some new data. Refresh!!
Thank you for your help!
In theory, this is exactly what I wanted. However, in practice, I am struggling to apply it to my workbook.
Which M-code am I supposed to be adjusting? The one of "tAnnotated", the one of my original Query, or am I supposed to create a new query from the table, like with the self-referencing query?
