Consuming XML web service

[YCastano]

Hello everyone!

I need to consume data from a XML web service that generates what I show in the first image. The data is in the second table, but it only shows me 100 records, when there are almost 900. 

When I reviewed the first table, see inside the "filter" column that there are values referring to the page and the number of records..

I think these values are limiting the data shown in the second table, the problem is that I don't know how to modify these values to see if shows me all the records.  Any help?

Tks.

[YCastano · Accepted solution]

Finally, I solved it by generating a Power Query loop to fetch all pages automatically, and later adding a counter for consulting only the last page and finally combining them. Here is the complete code:

let
    BaseUrl = "https://xxxxxxxxxxxxxxxxxx",
   
    // Función para obtener la página de datos
    GetPage = (pageNumber) =>
        let
            // Realiza la solicitud con los parámetros adecuados
            Response = Web.Contents(BaseUrl, [Query=[page=Number.ToText(pageNumber)]]),
            // Convierte la respuesta XML en una tabla
            Data = Xml.Tables(Response),
            // La primera tabla contiene los parámetros de la consulta (paginación y demás)
            RequestInfo = Data{0}[Table],
            ExpandedFilter = Table.ExpandTableColumn(RequestInfo, "filter", {"effective_date", "changed_on_or_after", "changed_before", "change_type", "deleted", "only_deleted", "page", "max_page_size", "actual_page_size", "more_pages"}, {"effective_date", "changed_on_or_after", "changed_before", "change_type", "deleted", "only_deleted", "page", "max_page_size", "actual_page_size", "more_pages"}),
            // La segunda tabla contiene los datos
            DataTable = Data{1}[Table],
            Table1 = DataTable[Table],
            // Expande la tabla de datos
            ExpandedDataTable = Table.SelectColumns(ExpandedFilter, {"page", "more_pages"}),
            // Devuelve los datos junto con la información de la paginación
            PageData = [Data = DataTable, Pagination = ExpandedDataTable]
        in
            PageData,

    // Generar la lista de todas las páginas de datos
    AllData = List.Generate(// Estado inicial con la primera página
    () => [Page = Number.FromText(GetPage(1)[Pagination][page]{0}), Data = GetPage(1)[Data], MorePages = GetPage(1)[Pagination][more_pages]{0}],
    
    // Condición de continuidad: continuar mientras MorePages sea 1 (hay más páginas) 
    each Number.FromText([MorePages]) = 1, 
    
    each   // Actualización del estado: aumentar la página y obtener los datos correspondientes
        let
           NextPage = [Page] + 1, 
           PageData = GetPage(NextPage),
           NewMorePages = PageData[Pagination][more_pages]{0}
        in 
            [Page = NextPage, Data = PageData[Data], MorePages = NewMorePages],  
            
        // Extraer los datos de cada página
        each [Data]
    ),

    // Para capturar el núnmero de páginas, execptuando la última 
    Contador = List.Count(AllData),

    // Combinar las tablas de datos de cada página en una tabla
    ExpandedData = Table.Combine(AllData),

    // Combinar y Expandir todas las tablas de registros
    Table1 = Table.Combine(ExpandedData[Table]),

    //Utilizo el contador para acceder a la última página
    LastPage = [Data = GetPage(Contador + 1)[Data], MorePages = GetPage(Contador + 1)[Pagination][more_pages]{0}],
    AllData2 = LastPage[Data],
    Table2 = AllData2{0}[Table],

    // Combinar las tablas de la primera parte con la tabla de la ultima página
    CombinedData = Table.Combine({Table1, Table2}),
    
in
    CombinedData 

[v-pbandela-msft]

Hi @YCastano,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please "Accept  as  Solution" and give a 'Kudos' so other members can easily find it.

Thank you,
Pavan.

[v-pbandela-msft]

Hi @YCastano,

Could you please confirm if this issue has been resolved? If it has, kindly mark the helpful reply and "Accept as  Solution". This will assist other community members in resolving similar problems more quickly.

Thank you,
Pavan.

[YCastano]

Hi @v-pbandela-msft thank you.

I think it doesn't work, it seems that it stays in loop because it stays thinking and does not generate the list of tables, or gives me List error. I had also tried to put the (1 or 0) and the same thing happened, I think it stays in a loop.

[v-pbandela-msft]

Hi @YCastano,

Thank you for your patience. I understand that the query seems to be stuck in an infinite loop. This may be happening due to how MorePages is being checked within List.Generate.

Try the following adjustments to prevent the loop:

1. Instead of checking MorePages = 1, use MorePages <> 0 to ensure it stops when there are no more pages.

2. Use try ... otherwise 0 to prevent errors when accessing more_pages.

here is the updated Code

let
BaseUrl = "https://xxxxxxxxxx",

// Function to retrieve a page of data
GetPage = (pageNumber) =>
let
Response = Web.Contents(BaseUrl, [Query=[page=Number.ToText(pageNumber), max_page_size="1000"]]),
Data = Xml.Tables(Response),
RequestInfo = Data{0}[Table],
ExpandedFilter = Table.ExpandTableColumn(RequestInfo, "filter",
{"effective_date", "changed_on_or_after", "changed_before", "change_type",
"deleted", "only_deleted", "page", "max_page_size", "actual_page_size", "more_pages"},
{"effective_date", "changed_on_or_after", "changed_before", "change_type",
"deleted", "only_deleted", "page", "max_page_size", "actual_page_size", "more_pages"}
),
DataTable = Data{1}[Table],
ExpandedDataTable = Table.SelectColumns(ExpandedFilter, {"page", "max_page_size", "actual_page_size", "more_pages"}),
PageData = [Data = DataTable, Pagination = ExpandedDataTable]
in
PageData,

// Generate a list of all pages of data
AllData = List.Generate(
() =>
let
FirstPageData = GetPage(1)
in
[Page = 1, Data = FirstPageData[Data], MorePages = try FirstPageData[Pagination][more_pages]{0} otherwise 0],

// **Stopping condition: Continue only if MorePages is not 0**
each [MorePages] <> 0,

each
let
NextPage = [Page] + 1,
PageData = GetPage(NextPage),
NewMorePages = try PageData[Pagination][more_pages]{0} otherwise 0
in
[Page = NextPage, Data = PageData[Data], MorePages = NewMorePages],

each [Data] // Extract data from each page
),

// Combine data from all pages
ExpandedData = Table.Combine(AllData),
CombinedData = Table.Combine(ExpandedData[Table]),

// Remove empty records
Results = Table.SelectRows(CombinedData, each ([unidadorg] <> ""))
in
Results

Please continue using Microsoft community forum.

If you found this post helpful, please consider marking it as "Accept as Solution" and give it a 'Kudos'. if it was helpful. help other members find it more easily.

Regards,
Pavan.

[YCastano]

Hi @v-pbandela-msft, With the value [MorePages] <> 0, I still have the last page not included, since the last page has the value [MorePages] = 0.

[Fowmy]

@YCastano 

Looks like the XML web service is paginating the data, showing only 100 records at a time. The "filter" column likely has parameters for page number and record limit. Try modifying these values like increasing limit=900 or looping through pages (page=1, 2, 3...) to get all data. You can create a Power Query loop to fetch all pages automatically.

If you could share your M code, I will be able to see how this is set up

[YCastano]

Hi Fowmy, I was trying to do something like this, but I get a mistake creating the list 

let
    BaseUrl = "https://xxxxxx",
   
    // Función para obtener la página de datos
    GetPage = (pageNumber) =>
        let
            // Realiza la solicitud con los parámetros adecuados
            Response = Web.Contents(BaseUrl, [Query=[page=Number.ToText(pageNumber), max_page_size="1000"]]),
            // Convierte la respuesta XML en una tabla
            Data = Xml.Tables(Response),
            // La primera tabla contiene los parámetros de la consulta (paginación y demás)
            RequestInfo = Data{0}[Table],
            // La segunda tabla contiene los datos
            DataTable = Data{1}[Table],
            //Table = RequestInfo{0}[Table],
            Table1 = DataTable{0}[Table],
            // Expande la tabla de datos
            ExpandedDataTable = Table.ExpandRecordColumn(RequestInfo, "filter", {"page", "max_page_size", "actual_page_size", "more_pages"}),
            // Devuelve los datos junto con la información de la paginación
            PageData = [Data = Table1, Pagination = ExpandedDataTable]
        in
            PageData,

    // Generar la lista de todas las páginas de datos
    AllData = List.Generate(
        () => [Page = 1, Data = GetPage(1)[Data], MorePages = GetPage(1)[Pagination][more_pages]{0}], 
        each [MorePages] = 1, // Continuar mientras haya más páginas
        each [Page = [Page] + 1, Data = GetPage([Page])[Data], MorePages = GetPage([Page])[Pagination][more_pages]{0}], 
        each [Data] // Obtener los datos de cada página
    ),
    
    // Expandir las tablas de datos de cada página en una lista de registros (filas)
    ExpandedData = List.Transform(AllData, each Table.ToList(_)),
    
    // Combinar todas las listas de registros
    CombinedData = List.Combine(ExpandedData),
    
    // Convertir la lista de registros combinados en una tabla
    Result = Table.FromList(CombinedData, Splitter.SplitByNothing())
in
    Result

I was following an internet page, but maybe I need to expand the columns in the first table?

[Fowmy]

@YCastano 

Table1 = DataTable{0}[Table] seems to be extracting the first row of DataTable as a table. Make sure that this extraction is correct.

Have a look at this part in Li.Generate MorePages = GetPage(1)[Pagination][more_pages]{0}
MorePages to be a binary value (1 or 0) indicating whether there are more pages

In the line ExpandedDataTable = Table.ExpandRecordColumn(RequestInfo, "filter", {"page", "max_page_size", "actual_page_size", "more_pages"}), you're expanding RequestInfo to include pagination data (page, max_page_size, actual_page_size, more_pages). This is fine, but check if all these columns exist in your response XML, as missing columns could lead to errors.

[YCastano]

I have modified the code

let
    BaseUrl = "https://xxxxxxxxxx",
   
    // Función para obtener la página de datos
    GetPage = (pageNumber) =>
        let
            // Realiza la solicitud con los parámetros adecuados
            Response = Web.Contents(BaseUrl, [Query=[page=Number.ToText(pageNumber), max_page_size="1000"]]),
            // Convierte la respuesta XML en una tabla
            Data = Xml.Tables(Response),
            // La primera tabla contiene los parámetros de la consulta (paginación y demás)
            RequestInfo = Data{0}[Table],
            ExpandedFilter = Table.ExpandTableColumn(RequestInfo, "filter", {"effective_date", "changed_on_or_after", "changed_before", "change_type", "deleted", "only_deleted", "page", "max_page_size", "actual_page_size", "more_pages"}, {"effective_date", "changed_on_or_after", "changed_before", "change_type", "deleted", "only_deleted", "page", "max_page_size", "actual_page_size", "more_pages"}),
            // La segunda tabla contiene los datos
            DataTable = Data{1}[Table],
            Table1 = DataTable[Table],
            // Expande la tabla de datos
            ExpandedDataTable = Table.SelectColumns(ExpandedFilter, {"page", "max_page_size", "actual_page_size", "more_pages"}),
            // Devuelve los datos junto con la información de la paginación
            PageData = [Data = DataTable, Pagination = ExpandedDataTable]
        in
            PageData,

    // Generar la lista de todas las páginas de datos
    AllData = List.Generate(// Estado inicial con la primera página#
    () => [Page = Number.FromText(GetPage(1)[Pagination][page]{0}), Data = GetPage(1)[Data], MorePages = GetPage(1)[Pagination][more_pages]{0}],
    
    // Condición de continuidad: continuar mientras MorePages sea 1 (hay más páginas) 
    each Number.FromText([MorePages]) = 1, 
    
    each   // Actualización del estado: aumentar la página y obtener los datos correspondientes
        let
           NextPage = [Page] + 1, 
           PageData = GetPage(NextPage),
           NewMorePages = PageData[Pagination][more_pages]{0}
        in 
            [Page = NextPage, Data = PageData[Data], MorePages = NewMorePages],  
            
        // Extraer los datos de cada página
        each [Data]
    ),

    // Combinar las tablas de datos de cada página en una tabla
    ExpandedData = Table.Combine(AllData),
    
    // Combinar y Expandir todas las tablas de registros
    CombinedData = Table.Combine(ExpandedData[Table]),
    Results = Table.SelectRows(CombinedData, each ([unidadorg] <> ""))
in
    Results

and proved that the GetData function works well, it generates the following:

Now I'm leaving out the last page of the web service (page 10), since it has the value [more_pages] = 0. How can I include it?

[v-pbandela-msft]

Hi @YCastano,

Thank you for reaching out in Microsoft Community Forum.

Thank you  @Fowmy for the helpful response.

Please Modify the List.Generate function so that the loop includes the last page even when more_pages = 0.

Here is the modified M code;

let
BaseUrl = "https://xxxxxxxxxx",

// Function to retrieve a page of data
GetPage = (pageNumber) =>
let
Response = Web.Contents(BaseUrl, [Query=[page=Number.ToText(pageNumber), max_page_size="1000"]]),
Data = Xml.Tables(Response),
RequestInfo = Data{0}[Table],
ExpandedFilter = Table.ExpandTableColumn(RequestInfo, "filter", {"effective_date", "changed_on_or_after", "changed_before", "change_type", "deleted", "only_deleted", "page", "max_page_size", "actual_page_size", "more_pages"}, {"effective_date", "changed_on_or_after", "changed_before", "change_type", "deleted", "only_deleted", "page", "max_page_size", "actual_page_size", "more_pages"}),
DataTable = Data{1}[Table],
Table1 = DataTable[Table],
ExpandedDataTable = Table.SelectColumns(ExpandedFilter, {"page", "max_page_size", "actual_page_size", "more_pages"}),
PageData = [Data = DataTable, Pagination = ExpandedDataTable]
in
PageData,

// Generate a list of all pages of data
AllData = List.Generate(
() =>
let
FirstPageData = GetPage(1)
in
[Page = 1, Data = FirstPageData[Data], MorePages = FirstPageData[Pagination][more_pages]{0}],

// **Fix: Change stopping condition to ensure last page is included**
each [MorePages] <> null, // Continue as long as we retrieve data

each
let
NextPage = [Page] + 1,
PageData = GetPage(NextPage),
NewMorePages = PageData[Pagination][more_pages]{0}
in
[Page = NextPage, Data = PageData[Data], MorePages = NewMorePages],

each [Data] // Extract data from each page
),

// Combine data from all pages
ExpandedData = Table.Combine(AllData),
CombinedData = Table.Combine(ExpandedData[Table]),

// Remove empty records
Results = Table.SelectRows(CombinedData, each ([unidadorg] <> ""))
in
Results

Modified the stopping condition in List.Generate to continue looping as long as data is retrieved, ensuring the last page is included.

Please continue using Microsoft community forum.

If you found this post helpful, please consider marking it as "Accept as Solution" and give it a 'Kudos'. if it was helpful. help other members find it more easily.

Regards,
Pavan.

[YCastano]

Hi @v-pbandela-msft thank you.

I think it doesn't work, it seems that it stays in loop because it stays thinking and does not generate the list of tables. I had also tried to put the (1 or 0) and the same thing happened, I think it stays in a loop.