Power Query not recognising data where PowerBI does

Question

I wish to extract the data from this website:

https://apps.who.int/food-additives-contaminants-jecfa-database/search.aspx?fl=%2b

When I do so in Power query I only get the Kind, Name, Children, Text table.

However when doing the exact same on PowerBI this recognises the list as desired.

Can I get Power Query to recognise the data in the same way? Or is there a way to export the Query to Excel?

Ron Rosenfeld · Accepted Answer · 2021-05-06 20:30:13Z

2

It seems that Power Query in Excel does not have the Html.Table function which is used in Power Query in Power BI.

But you can export the data as a csv, then import into Excel.

From the PQ Editor:

Close and Apply
Visualize all the columns
Click in the visualized area
At the bottom right, you will see an ellipsis

Click there and you will be able to select Export to CSV

answered May 6, 2021 at 20:30

Ron Rosenfeld

62k7 gold badges35 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Nick Over a year ago

Hi Thank you! I thought this might be the case. Also, do you know how I might be able to compare the two generated columns and transform the data such that one column for example would read (+)-alpha-TOCOPHEROL and the other (0.15-2 mg/kg bw) etc. Unfortunately I didn't realise this when posting this question.

Ron Rosenfeld Over a year ago

@Nick I can think of any number of ways to do this. Power Query (either in Power BI or Excel), VBA, even Excel formulas. But I think this warrants a different question where you show what you have, what you want, what you've tried, and where you've run into problems with what you've tried. Before you create your question, it might read the HELP pages for information as to How to Ask a Good Question, and How to create a Minimal, Complete, and Verifiable example, so as to obtain more focused assistance.

Nick · Accepted Answer · 2021-05-07 12:46:39Z

Bit of a convoluted solution but I have subsequently found how to achieve this using Power Query in Excel. It is possible to navigate the HTML within power query to get to the raw data. Once here the data may not be adjacent to one another but this can be cleaned up easily using power query.

M Code:

let
    Source = Web.Page(Web.Contents("https://apps.who.int/food-additives-contaminants-jecfa-database/search.aspx?fcc=4")),
    Data0 = Source{0}[Data],
    Children0 = Data0{0}[Children],
    Children1 = Children0{1}[Children],
    Children2 = Children1{2}[Children],
    Children3 = Children2{2}[Children],
    Children6 = Children3{6}[Children],
    Children4 = Children6{6}[Children],
    Children5 = Children4{1}[Children],
    Children19 = Children5{19}[Children],
    Children7 = Children19{1}[Children],
    #"Expanded Children" = Table.ExpandTableColumn(Children7, "Children", {"Kind", "Name", "Children", "Text"}, {"Children.Kind", "Children.Name", "Children.Children", "Children.Text"}),
    #"Expanded Children.Children" = Table.ExpandTableColumn(#"Expanded Children", "Children.Children", {"Kind", "Name", "Children", "Text"}, {"Children.Children.Kind", "Children.Children.Name", "Children.Children.Children", "Children.Children.Text"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Children.Children",{"Kind", "Name", "Children.Kind", "Children.Name", "Children.Children.Kind", "Children.Children.Name", "Children.Children.Children", "Text"}),
    #"Filled Up" = Table.FillUp(#"Removed Columns",{"Children.Text"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Up", each ([Children.Children.Text] <> null and [Children.Children.Text] <> "Flavouring Agent"))
in
    #"Filtered Rows"

Collectives™ on Stack Overflow

Power Query not recognising data where PowerBI does

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related