Forum Discussion
Azure Table Storage to Power BI
A dataset having 7000+ rows and increasing by 1500 rows per day in azure table storage - is pushing data to power bi desktop by SAS token URL - a M-Code is created in Advanced Editor which transforms the table storage data and receicing only 2378 rows of data despite changing M-code mutiple times. What maybe the issue? why power bi is unable to get the complete data of 7000+ rows as on time. Advise resolution.
Hi rooftopurja ,
Thank you for sharing your experience and challenges with using SAS tokens in M-Code for Azure Table Storage, when dealing with pagination and authentication. As you've pointed out, this approach can often be error-prone and tricky to implement.
While SAS tokens work smoothly in Lua scripts for your IoT Gateway, Power BI’s handling of these tokens, particularly in conjunction with pagination, can introduce limitations and complexities. This is why Dataflows can often provide a more reliable and seamless solution, as they are designed to manage both authentication and pagination more efficiently.
It's great to hear that Dataflows have resolved your issue. As you've found, Dataflows streamline the connection to Azure Table Storage by automatically handling authentication, data retrieval, and pagination, eliminating the need to manually manage SAS tokens and continuation tokens in M-Code.For large datasets or cases involving complex data sources, Dataflows are often the most effective approach to ensure smooth, reliable data flows into Power BI.
If you have any further questions or need assistance, feel free to reach out.
If this reply was helpful, please consider marking it as the accepted solution to assist others who may have similar queries.
Best regards,
Vinay.
4 Replies
Note that the datasize is within limits 23MB as on time - and using PPU license for Power BI.
Even after modifying M-Code multiple times, rows were restricted and had a tough time. Finally, i shifted to Dataflows which manages authentication with Azure Table Storage URL and i could download all devices data and working seamlessly. Working with SAS tokens in M-Code is prone to errors and not easy to implement. Same SAS tokens - We are using in Lua Scripts in out IOT Gateway to get real-time data into Azure Table Storage and there are no issues at all.
Hi rooftopurja ,
Thank you for sharing your experience and challenges with using SAS tokens in M-Code for Azure Table Storage, when dealing with pagination and authentication. As you've pointed out, this approach can often be error-prone and tricky to implement.
While SAS tokens work smoothly in Lua scripts for your IoT Gateway, Power BI’s handling of these tokens, particularly in conjunction with pagination, can introduce limitations and complexities. This is why Dataflows can often provide a more reliable and seamless solution, as they are designed to manage both authentication and pagination more efficiently.
It's great to hear that Dataflows have resolved your issue. As you've found, Dataflows streamline the connection to Azure Table Storage by automatically handling authentication, data retrieval, and pagination, eliminating the need to manually manage SAS tokens and continuation tokens in M-Code.For large datasets or cases involving complex data sources, Dataflows are often the most effective approach to ensure smooth, reliable data flows into Power BI.
If you have any further questions or need assistance, feel free to reach out.
If this reply was helpful, please consider marking it as the accepted solution to assist others who may have similar queries.
Best regards,
Vinay.
Hi rooftopurja ,
Thank you for reaching out to the Fabric Community.
Based on your description, you're able to retrieve 2378 rows, but you're not getting the full 7000+ rows. This indicates that the pagination is partially working, but it seems Power BI is not handling continuation tokens properly to fetch additional rows beyond what has already been loaded.
Azure Table Storage has a default limit of 1,000 rows per query response. If you have more than 1,000 rows, the API will return a continuation token in the response (NextPartitionKey and NextRowKey) to fetch the next page of results. Power BI does not automatically handle these continuation tokens unless your M code is explicitly written to process them and fetch subsequent pages.Since you're able to retrieve 2378 rows, it suggests that pagination is occurring for at least the first set of rows, but Power BI is likely not continuing to the next pages after that. You can find more information on the pagination and query limits in Query timeout and pagination (REST API) - Azure Storage | Microsoft Learn
To retrieve the entire dataset, you'll need to implement pagination handling in Power Query (M code). By doing this, you can ensure that Power BI will correctly follow the continuation tokens and load all available data.Here are some helpful resources:
Re: how to create a query that paginates? - Page 2 - Microsoft Fabric Community: This discussion on creating a query that paginates provides guidance on how to set up recursive queries for pagination.
You can also check out the Medium article by Martijn Lentink on using Azure Table Storage with SAS tokens in Power BI, which offers a detailed explanation of how to handle pagination and retrieve all rows.
Hope this helps. Please reach out for further assistance.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly and a kudos would be appreciated.Thank you.
