CODEWITHSUNDEEP
excelpowerbipowerquery
Nick
Nick

Reputation: 875

Power Query not recognising data where PowerBI does

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. enter image description here

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

enter image description here

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

Upvotes: 0

Views: 667

Answers (2)

Nick
Nick

Reputation: 875

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"

Upvotes: 1

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60444

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

enter image description here

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

Upvotes: 2

Related Questions