Changing data from imported websites

Juri_Messenboeck 

And there is much easier solution - instead of using default US region in URL like for Berlin

https://weather.com/weather/tenday/l/Berlin+Germany?canonicalCityId=49875d033efa3f8a515cc07b7ab1897df30ce65616175ffb4591cb95c5f646ff

use the link with your preferable region/language, e.g. UK/English URL for the Berlin will be

https://weather.com/en-UK/weather/tenday/l/5ca23443513a0fdc1d37ae2ffaf5586162c6fe592a66acc9320a0d0536be1bb9

(region code after weather.com/).

Result will be in units for this region. I have no idea why km/h is default for UK and why US and UK forecast for Berlin are bit different. Script is simple then

let
    Source = Web.Page(Web.Contents("https://weather.com/en-UK/weather/tenday/l/5ca23443513a0fdc1d37ae2ffaf5586162c6fe592a66acc9320a0d0536be1bb9")),
    Data0 = Source{0}[Data],
    ColumnNames=List.FirstN(Table.ColumnNames(Data0),6),
    RemoveDayColumn = Table.RemoveColumns(Data0,{"Day"}),
    TempColumnNames = Table.ColumnNames(RemoveDayColumn),
    ListOfList = List.Zip({TempColumnNames,ColumnNames}),
    RenameTableColumns = Table.RenameColumns(RemoveDayColumn, ListOfList),
    DayInOneString = Table.ReplaceValue(
        RenameTableColumns,
        "#(cr)#(lf)",
        " ",
        Replacer.ReplaceText,{"Day"}
    )
in
    DayInOneString

See in second sheet for Berlin attached.

Juri_Messenboeck 

Changing units on site is done by java script, it doesn't affect returned table.

 

It's much better to do all transformations within the query - more reliable and to avoid risk of not syncing added data with returned by query.

 

I did that for Berlin, you may check steps in attached file. Script is

let
    Source = Web.Page(Web.Contents("https://weather.com/weather/tenday/l/Berlin+Germany?canonicalCityId=49875d033efa3f8a515cc07b7ab1897df30ce65616175ffb4591cb95c5f646ff")),
    Data0 = Source{0}[Data],
    ColumnNames=List.FirstN(Table.ColumnNames(Data0),6),
    RemoveDayColumn = Table.RemoveColumns(Data0,{"Day"}),
    TempColumnNames = Table.ColumnNames(RemoveDayColumn),
    ListOfList = List.Zip({TempColumnNames,ColumnNames}),
    RenameTableColumns = Table.RenameColumns(RemoveDayColumn, ListOfList),
    SplitWind = Table.SplitColumn(
        RenameTableColumns,
        "Wind",
        Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv),
        {"Wind.1", "Wind.2", "Wind.3"}
    ),
    VelocityToNumber = Table.TransformColumnTypes(
        SplitWind,
        {
            {"Wind.1", type text},
            {"Wind.2", Int64.Type},
            {"Wind.3", type text}
        }
    ),
    MphToKmh = Table.AddColumn(
        VelocityToNumber,
        "Custom",
        each Number.ToText([Wind.2]*1.60934,"F1")
    ),
    ReplaceUnit = Table.ReplaceValue(
        MphToKmh,
        "mph","km/h",
        Replacer.ReplaceText,{"Wind.3"}
    ),
    AddNewWind = Table.AddColumn(
        ReplaceUnit,
        "Wind",
        each Text.Combine(
            {[Wind.1], [Custom], [Wind.3]}, " "
        ),
        type text
    ),
    ReplaceNoTempreture = Table.ReplaceValue(
        AddNewWind,
        "--","--°",
        Replacer.ReplaceText,
        {"High / Low"}
    ),
    SplitByDegreeCharacter = Table.SplitColumn(
        ReplaceNoTempreture,
        "High / Low",
        Splitter.SplitTextByDelimiter("°", QuoteStyle.Csv),
        {"High/Low.1", "High/Low.2", "High/Low.3"}
    ),
    DegreeToNumber = Table.TransformColumnTypes(
        SplitByDegreeCharacter,
        {
            {"High/Low.1", Int64.Type},
            {"High/Low.2", Int64.Type},
            {"High/Low.3", type text}
        }
    ),
    HighDfToDc = Table.ReplaceValue(
        DegreeToNumber,
        each [#"High/Low.1"],
        each Number.ToText(([#"High/Low.1"] - 32)*5/9,"F1"),
        Replacer.ReplaceValue,{"High/Low.1"}
    ),
    LowDfToDc = Table.ReplaceValue(
        HighDfToDc,
        each [#"High/Low.2"],
        each Number.ToText(([#"High/Low.2"] - 32)*5/9,"F1"),
        Replacer.ReplaceValue,{"High/Low.2"}
    ),
    ReplacErrors = Table.ReplaceErrorValues(
        LowDfToDc,
        {
            {"High/Low.1", "--"},
            {"High/Low.2", "--"}
        }
    ),
    InsertNewHighLow = Table.AddColumn(
        ReplacErrors,
        "High / Low",
        each Text.Combine({[#"High/Low.1"], [#"High/Low.2"]}, "°") & "°",
        type text
    ),
    DayInOneString = Table.ReplaceValue(
        InsertNewHighLow,
        "#(cr)#(lf)",
        " ",
        Replacer.ReplaceText,{"Day"}
    ),
    RemoveUnused = Table.SelectColumns(
        DayInOneString,
        {"Day", "Description", "High / Low", "Wind", "Precip", "Humidity"}
    )
in
    RemoveUnused