Consuming XML web service

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.

Need Help

YCastano

1 year ago

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

12 Replies

Fowmy

Super User

1 year ago

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

Frequent Visitor

1 year ago

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
Super User
1 year ago
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.

Anonymous
Not applicable
1 year ago
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
  Frequent Visitor
  1 year ago
  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.
  - Anonymous
    Not applicable
    1 year ago
    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.
Anonymous
Not applicable
1 year ago
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.

YCastano

Frequent Visitor

1 year ago

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

Forum Discussion

Consuming XML web service

12 Replies

Related content

SOAP web services XML

Consuming compressed messages from Event Hub

XML import problem

XML parsing fabric notebooks

Gallery Terms of Service