How do you get a custom PowerQuery function that returns a one cell Table to return text instead

Question

I'm trying to teach myself to write PowerQuery M functions by scraping suggestions listed at the PowerBI Ideas website. I come from a VBA background, so M is quite foreign to me.

I've got a function that creates a dynamic URL given a Category_ID and PageNumber that I feed to it. I have another couple of functions that retrieve the Title and Description of a particular suggestion from the URL, based on where it lies on the page (i.e. these functions can get the first item on a page, or the 2nd, or say the 20th).

One thing I've noticed is that my funciton that creates the dynamic URL returns text, whereas the other two return tables with just one thing in them:

I know I can simply expand those Title and Description columns after the fact:

...but I'm curious if there is a step I can add to my GetTitle and GetDescription functions so that they return text, like the GenerateURL function does?

Here's the functions:

let GenerateURL = (Category_ID, PageNo) =>
    let
        Source = "https://ideas.powerbi.com/forums/265200-power-bi-ideas/category/" & Number.ToText(Category_ID) & "/filters/top?page=" & Number.ToText(PageNo)
    in Source
in GenerateURL

let GetTitle = (URL as text, Element as number) =>
    let
        Source = Web.Page(Web.Contents(URL)),
        Data0 = Source{0}[Data],
        Children = Data0{0}[Children],
        Children1 = Children{2}[Children],
        Children2 = Children1{3}[Children],
        Children3 = Children2{7}[Children],
        Children4 = Children3{1}[Children],
        Children5 = Children4{1}[Children],
        Children6 = Children5{2}[Children],
        Children7 = Children6{10}[Children],
        Children8 = Children7{3}[Children],
        Children9 = Children8{Element}[Children],
        Children10 = Children9{1}[Children],
        Children11 = Children10{1}[Children],
        Children12 = Children11{0}[Children],
        #"Removed Other Columns" = Table.SelectColumns(Children12,{"Text"})
    in
        #"Removed Other Columns"
in GetTitle



let GetDescription = (URL as text, Element as number) =>
    let
        Source = Web.Page(Web.Contents(URL)),
        Data0 = Source{0}[Data],
        Children = Data0{0}[Children],
        Children1 = Children{2}[Children],
        Children2 = Children1{3}[Children],
        Children3 = Children2{7}[Children],
        Children4 = Children3{1}[Children],
        Children5 = Children4{1}[Children],
        Children6 = Children5{2}[Children],
        Children7 = Children6{10}[Children],
        Children8 = Children7{3}[Children],
        Children9 = Children8{Element}[Children],
        Children10 = Children9{1}[Children],
        Children11 = Children10{3}[Children],
        Children12 = Children11{1}[Children],
        #"Expanded Children1" = Table.ExpandTableColumn(Children12, "Children", {"Children", "Text"}, {"Children.Children", "Children.Text"}),
        #"Expanded Children.Children" = Table.ExpandTableColumn(#"Expanded Children1", "Children.Children", {"Children", "Text"}, {"Children.Children.Children", "Children.Children.Text"}),
        #"Added Custom" = Table.AddColumn(#"Expanded Children.Children", "Coalesce", each if [Children.Children.Text] <> null then [Children.Children.Text] else if [Children.Text] <> null  then [Children.Text] else [Text]),
        #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Coalesce] <> null)),
        #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Coalesce"}),
        #"Transposed Table" = Table.Transpose(#"Removed Other Columns"),
        #"Added Custom1" = Table.AddColumn(#"Transposed Table", "Custom", each ""),
        #"Merged Columns" = Table.CombineColumns(#"Added Custom1",Table.ColumnNames(#"Added Custom1"),Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged")
    in
        #"Merged Columns"
in GetDescription

...and here's an example of how I'm calling them (including the expansion steps):

let
    #"Temp Table" = #table(
 type table
    [
        #"Cat_ID"=number, 
        #"PageNo"=number,
        #"Element"=number
    ], 

  {{180799,1,1},{180799,1,3}}

),
    #"Invoked Custom Function" = Table.AddColumn(#"Temp Table", "URL", each MakeURL([Cat_ID], [PageNo])),
    #"Invoked Custom Function1" = Table.AddColumn(#"Invoked Custom Function", "Title", each GetTitle([URL], [Element])),
    #"Invoked Custom Function2" = Table.AddColumn(#"Invoked Custom Function1", "Description", each GetDescription([URL], [Element])),
    #"Expanded Title" = Table.ExpandTableColumn(#"Invoked Custom Function2", "Title", {"Text"}, {"Text"}),
    #"Expanded Description" = Table.ExpandTableColumn(#"Expanded Title", "Description", {"Merged"}, {"Merged"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Description",{{"Text", "Title"}, {"Merged", "Description"}})
in
    #"Renamed Columns"

Alexis Olson Alexis Olson · Accepted Answer · 2018-02-16T04:44:59

If you tell your functions to take the first (0th) row of the column that they return, then you shouldn't need to expand.

In your GetTitle function change this line

#"Removed Other Columns" = Table.SelectColumns(Children12,{"Text"})

to the following by adding {0}[Text] onto the end:

#"Removed Other Columns" = Table.SelectColumns(Children12,{"Text"}){0}[Text]

Similarly, you can change the final line in your GetDescription function:

#"Merged Columns" = Table.CombineColumns(#"Added Custom1",[...],"Merged"){0}[Merged]

Once you do this, you shouldn't need the last 3 steps of your query where you call these functions.

How do you get a custom PowerQuery function that returns a one cell Table to return text instead

1 Answers