Discussions

Hola, me está saliendo este error al hacer unos queries en EXCEL, pero eso no me salía ayer, no sé si esté fallando algo. De hecho me carga la info en el editor de power query pero si le doy actualizar o lo quiero cargar a una hoja de excel me marca el error. Gracias por su ayuda

---------- Mensaje ----------
[DataSource.Error] nodename nor servname provided, or not known (www.tokkobroker.com:443)

---------- Id. de sesión ----------
f6ac2b75-232a-4ac3-a9c3-3b822e5bf4ec

---------- Script de mashup ----------


section Section1;

shared cfgTokko = let
    cfgTokko = [
        ApiKey = "XXXXXXX",
        BaseUrl = "https://www.tokkobroker.com/api/v1/"
    ]
in
    cfgTokko;

shared Property = let
    // ===== CONFIG =====
    cfg = cfgTokko,
    ApiKey = cfg[ApiKey],
    BaseUrl = cfg[BaseUrl],

    // ===== FUNCIÓN PAGINADA =====
    TokkoGetPage = (Offset as number) =>
        let
            Source = Json.Document(
                Web.Contents(
                    BaseUrl & "contact/",
                    [
                        Query = [
                            key = ApiKey,
                            limit = "200",
                            offset = Text.From(Offset)
                        ]
                    ]
                )
            ),

            Results = Source[objects],
            Meta = Source[meta],
            Total = Meta[total_count]
        in
            [
                Results = Results,
                Total = Total
            ],

    // ===== PRIMERA PÁGINA =====
    FirstPage = TokkoGetPage(0),
    TotalRecords = FirstPage[Total],

    // ===== GENERAR OFFSETS =====
    Offsets = List.Generate(
        () => 0,
        each _ < TotalRecords,
        each _ + 200
    ),

    // ===== BAJAR TODAS LAS PÁGINAS =====
    Pages = List.Transform(
        Offsets,
        each TokkoGetPage(_)[Results]
    ),

    // ===== COMBINAR RESULTADOS =====
    Combined = List.Combine(Pages),

    // ===== TABLA BASE =====
    Tbl = Table.FromList(
        Combined,
        Splitter.SplitByNothing(),
        null,
        null,
        ExtraValues.Error
    ),

    // ===== EXPANDIR TODO NIVEL 1 =====
    Expand1 = Table.ExpandRecordColumn(
        Tbl,
        "Column1",
        Record.FieldNames(Combined{0}),
        Record.FieldNames(Combined{0})
    ),

    // ===== TAGS EN COLUMNAS, SIN DUPLICAR FILAS =====
    TagsComoListaDeNombres = Table.TransformColumns(
        Expand1,
        {
            {
                "tags",
                each
                    if _ = null then {}
                    else List.Transform(
                        _,
                        each
                            if Value.Is(_, type record) and Record.HasFields(_, "name")
                            then Text.From(_[name])
                            else Text.From(_)
                    ),
                type list
            }
        }
    ),

    MaxTags = 
        if Table.RowCount(TagsComoListaDeNombres) = 0 then
            0
        else
            List.Max(
                List.Transform(
                    TagsComoListaDeNombres[tags],
                    each List.Count(_)
                )
            ),

    TagsComoColumnas = Table.AddColumn(
        TagsComoListaDeNombres,
        "tags_columnas",
        each
            if List.Count([tags]) = 0 then
                []
            else
                Record.FromList(
                    [tags],
                    List.Transform(
                        {1..List.Count([tags])},
                        each "tag_" & Text.From(_)
                    )
                ),
        type record
    ),

    ExpandTags =
        if MaxTags = 0 then
            TagsComoColumnas
        else
            Table.ExpandRecordColumn(
                TagsComoColumnas,
                "tags_columnas",
                List.Transform({1..MaxTags}, each "tag_" & Text.From(_)),
                List.Transform({1..MaxTags}, each "tag_" & Text.From(_))
            ),

    // ===== EXPANDIR AGENT SI EXISTE =====
    ExpandAgent =
        if Table.HasColumns(ExpandTags, "agent") then
            Table.ExpandRecordColumn(
                ExpandTags,
                "agent",
                {"email", "name"},
                {"agent_email", "agent_name"}
            )
        else
            ExpandTags

in
    ExpandAgent;

shared Property1 = let
    // ===== CONFIG =====
    cfg = cfgTokko,
    ApiKey = cfg[ApiKey],
    BaseUrl = cfg[BaseUrl],

    // ===== FUNCIÓN PAGINADA =====
    TokkoGetPage = (Offset as number) =>
        let
            Source = Json.Document(
                Web.Contents(
                    BaseUrl,
                    [
                        RelativePath = "contact/",
                        Query = [
                            key = ApiKey,
                            limit = "200",
                            offset = Text.From(Offset)
                        ]
                    ]
                )
            ),
            Results = Source[objects],
            Meta = Source[meta],
            Total = Meta[total_count]
        in
            [
                Results = Results,
                Total = Total
            ],

    // ===== BAJAR TODO =====
    FirstPage = TokkoGetPage(0),
    TotalRecords = FirstPage[Total],

    Offsets = List.Generate(
        () => 0,
        each _ < TotalRecords,
        each _ + 200
    ),

    Pages = List.Transform(
        Offsets,
        each TokkoGetPage(_)[Results]
    ),

    Combined = List.Combine(Pages),

    Tbl = Table.FromList(
        Combined,
        Splitter.SplitByNothing(),
        null,
        null,
        ExtraValues.Error
    ),

    Expand1 = Table.ExpandRecordColumn(
        Tbl,
        "Column1",
        Record.FieldNames(Combined{0}),
        Record.FieldNames(Combined{0})
    ),

    // ===== TAGS EN COLUMNAS, SIN DUPLICAR FILAS =====
    TagsComoListaDeNombres = Table.TransformColumns(
        Expand1,
        {
            {
                "tags",
                each
                    if _ = null then {}
                    else List.Transform(
                        _,
                        each
                            if Value.Is(_, type record) and Record.HasFields(_, "name")
                            then Text.From(_[name])
                            else Text.From(_)
                    ),
                type list
            }
        }
    ),

    MaxTags = List.Max(
        List.Transform(
            TagsComoListaDeNombres[tags],
            each List.Count(_)
        )
    ),

    TagsComoColumnas = Table.AddColumn(
        TagsComoListaDeNombres,
        "tags_columnas",
        each Record.FromList(
            [tags],
            List.Transform(
                {1..List.Count([tags])},
                each "tag_" & Text.From(_)
            )
        ),
        type record
    ),

    ExpandTags = Table.ExpandRecordColumn(
        TagsComoColumnas,
        "tags_columnas",
        List.Transform({1..MaxTags}, each "tag_" & Text.From(_)),
        List.Transform({1..MaxTags}, each "tag_" & Text.From(_))
    ),
  #"agent expandido" = Table.ExpandRecordColumn(ExpandTags, "agent", {"email", "name"}, {"email.1", "name.1"})

in
    #"agent expandido";