excelvbatruncationexcel-web-query

Last Zeros Truncated When Getting External Data

While importing data from the web in Excel 2019 choosing Data>Get Data>From Other Sources>From Web, the last (trailing) zeros of numbers are being truncated resulting in the following 'Import' column: 

EU
Import | Desired
968,8  |  968800
891,01 |  891010
413,47 |  413470
410,3  |  410300
43,25  |   43250
17,8   |   17800
15,05  |   15050
3,61   |    3610
6,05   |    6050
4,9    |    4900

US
Import | Desired
968.8  |  968800
891.01 |  891010
413.47 |  413470
410.3  |  410300
43.25  |   43250
17.8   |   17800
15.05  |   15050
3.61   |    3610
6.05   |    6050
4.9    |    4900

I would like to convert the data which is text (commas, periods are remaining thousands separators), to numbers like in the Desired column.

I've overdone the following working VBA function:

Option Explicit

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Function UnTruncate(SourceVariant As Variant, _
  Optional TruncateString As String = "0", _
  Optional SplitSeparator As String = ",", _
  Optional NumberOfDigits As Long = 3) As Long

    Dim vnt As Variant        ' String Array (0-based, 1-dimensional)
    Dim strSource As String   ' Source String
    Dim strResult As String   ' Resulting String
    Dim strUB As String       ' Upper Bound String
    Dim i As Long             ' String Array Elements Counter

    ' Convert SourceVariant to a string (Source String (strSource)).
    strSource = CStr(SourceVariant)

    ' Check if Source String (strSource) is "" (UnTruncate = 0, by default).
    If strSource = "" Then Exit Function

    ' Split Source String (strSource) by SplitSeparator.
    vnt = Split(strSource, SplitSeparator)
    ' Assign the value of the last element in String Array (vnt)
    ' to Upper Bound String (strUB).
    strUB = vnt(UBound(vnt))

    ' Check if there is only one element in String Array (vnt). If so,
    ' write its value (strUB) to Resulting String (strResult) and go to
    ' ProcedureSuccess.
    If UBound(vnt) = 0 Then strResult = strUB: GoTo ProcedureSuccess

    ' Check if the length of Upper Bound String (strUB) is greater than
    ' NumberOfDigits. (UnTruncate = 0, by default)
    If Len(strUB) > NumberOfDigits Then Exit Function

    ' Add the needed number of TruncateStrings to Upper Bound String.
    strUB = strUB & String(NumberOfDigits - Len(strUB), TruncateString)

    ' Loop through the elements of String Array (vnt), from beginning
    ' to the element before the last, and concatenate them one after another
    ' to the Resulting String (strResult).
    For i = 0 To UBound(vnt) - 1: strResult = strResult & vnt(i): Next
    ' Add Upper Bound String (strUB) to the end of Resulting String (strResult).
    strResult = strResult & strUB

ProcedureSuccess:
    ' Convert Resulting String (strResult) to the resulting value of UnTruncate.
    UnTruncate = Val(strResult)

End Function
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

But I have a feeling I'm missing some important points.

I'm looking for other solutions: an improvement of my function, an Excel formula, a Power Query Solution, ... possibly when the data in Import column could be numbers or text.

Solution

  • It seems you were using the Legacy Wizard rather than Power Query.

    If you use Power Query, after selecting the Table, select Transform.

    Then, if the number column has been imported as text, and is showing the digits separator of the comma, don't remove the commas. Rather:

    enter image description here

    That should take care of things.

    EDIT:

    With regard to retaining hyperlinks from a web table using Power Query, it is not as straightforward as with the Legacy Wizard, but here is a method that seems to work with your source.

    It requires three queries and a function. And you will need to edit the table after the download to format the numbers, and possible the hyperlinks.

    ExcelTrim

    Enter the code below into the Advanced Editor of a Blank Query

    let ExcelTrim = (TextToTrim) =>
    let
        ReplacedText = Text.Replace(TextToTrim, "  ", " "),
        Result = if not(Text.Contains(ReplacedText, "  "))
            then ReplacedText
                else @ExcelTrim(ReplacedText)
    in
        Text.Trim(Result)
in
    ExcelTrim

    Table 0

    Note I used the Changed Type with Locale feature which should eliminate your dropped zero's problem.

    let
    Source = Web.Page(Web.Contents("https://kworb.net/youtube/")),
    Data = Source{0}[Data],
    #"Changed Type with Locale" = Table.TransformColumnTypes(Data, {{"Views", Int64.Type}, {"Likes", Int64.Type}}, "en-US"),
    #"Added Custom" = Table.AddColumn(#"Changed Type with Locale", "trimmedVideo", each ExcelTrim([Video]))
in
    #"Added Custom"

    getLinks

    let
    Source = Table.FromColumns({Lines.FromBinary(Web.Contents("https://kworb.net/youtube/"))}),
    #"Filtered Rows" = Table.SelectRows(Source, each Text.Contains([Column1], "href")),
    #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each Text.Contains([Column1], "<div><a href=")),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows1", "Link", each Text.BetweenDelimiters([Column1],"<a href=""","</a>")),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Added Custom", "Link", Splitter.SplitTextByEachDelimiter({""">"}, QuoteStyle.None, false), {"Link.1", "Link.2"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Link.1", type text}, {"Link.2", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Column1"}),
    #"Added Custom1" = Table.AddColumn(#"Removed Columns", "trimmedVideo", each ExcelTrim([Link.2])),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "normLinks", each if not Text.StartsWith([Link.1],"http") then 
    "https://kworb.net/youtube/" & [Link.1] else 
    [Link.1])
in
    #"Added Custom2"

    Merge1

    Returns the links in a separate column from the Videos

    let
    Source = Table.NestedJoin(#"Table 0", {"trimmedVideo"}, getLinks, {"trimmedVideo"}, "getLinks", JoinKind.LeftOuter),
    #"Added Custom" = Table.AddColumn(Source, "Links", each Table.Column([getLinks],"normLinks")),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Links", each Text.Combine(List.Transform(_, Text.From)), type text}),
    #"Removed Columns" = Table.RemoveColumns(#"Extracted Values",{"trimmedVideo", "getLinks"})
in
    #"Removed Columns"

    Alternatively you can use:

    Merge1 (2)

    Returns a HYPERLINK formula to the table which provides a clickable link with a friendly name.

    let
    Source = Table.NestedJoin(#"Table 0", {"trimmedVideo"}, getLinks, {"trimmedVideo"}, "getLinks", JoinKind.LeftOuter),
    #"Added Custom" = Table.AddColumn(Source, "Links", each Table.Column([getLinks],"normLinks")),
    #"Replaced Value" = Table.ReplaceValue(#"Added Custom","""","""""",Replacer.ReplaceText,{"Video"}),
    #"Extracted Values" = Table.TransformColumns(#"Replaced Value", {"Links", each Text.Combine(List.Transform(_, Text.From)), type text}),
    #"Removed Columns" = Table.RemoveColumns(#"Extracted Values",{"trimmedVideo", "getLinks"}),
    #"Added Custom1" = Table.AddColumn(#"Removed Columns", "Linked Videos", each "=HYPERLINK(""" & [Links] & """," & """" &[Video] & """)"),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom1",{{"Linked Videos", type text}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Changed Type",{"Video", "Links"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns1",{"", "2", "Linked Videos", "Views", "Likes"})
in
    #"Reordered Columns"

    If you use Merge1 (2) to get the hyperlinks, after saving, you will need to select the Linked Video column, and do a Find/Replace or = with = in order to turn the formula from a text string into a formula. If you refresh the query, you will need to repeat this process.

    You may also want to format the Views and Likes columns to show your thousands separators.

    Here is an example using `Merge1 (2) with the hyperlinks and my thousands separators.

    enter image description here