title: How do you get a custom PowerQuery function that returns a one cell Table to return text instead
body: <p>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. </p>
  
  <p>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).  </p>
  
  <p>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:</p>
  
  <p><a href="https://i.stack.imgur.com/DF4Rt.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/DF4Rt.jpg" alt="enter image description here"></a></p>
  
  <p>I know I can simply expand those Title and Description columns after the fact:</p>
  
  <p><a href="https://i.stack.imgur.com/jhCSH.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/jhCSH.jpg" alt="enter image description here"></a></p>
  
  <p>...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?</p>
  
  <p>Here's the functions: </p>
  
  <pre><code>let GenerateURL = (Category_ID, PageNo) =&gt;
      let
          Source = "https://ideas.powerbi.com/forums/265200-power-bi-ideas/category/" &amp; Number.ToText(Category_ID) &amp; "/filters/top?page=" &amp; Number.ToText(PageNo)
      in Source
  in GenerateURL
  
  let GetTitle = (URL as text, Element as number) =&gt;
      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) =&gt;
      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] &lt;&gt; null then [Children.Children.Text] else if [Children.Text] &lt;&gt; null  then [Children.Text] else [Text]),
          #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Coalesce] &lt;&gt; 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
  </code></pre>
  
  <p>...and here's an example of how I'm calling them (including the expansion steps):</p>
  
  <pre><code>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"
  </code></pre>