Hello all! Really new to Power BI, but I like what I see so far. To learn more I have been working through a personal use case and have a need to pull date from the web. The URL serves up pages of 200 entries at a time. There is a date field in the data and I am storing that in PowerBI. I've built a function to call so I can pass it the page numbers, so I can get all of the data and it works, but it is really inefficient.
A record from the web query looks like this:
{ "id": 2816445164, "item_id": 26559, "price": 2062, "quantity": 1, "created": "2023-02-03T18:07:34+00:00" }
I am doing this to get 100 pages:
let
Source = {0..100},
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "PageNumber"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"PageNumber", type text}}),
#"Invoked Custom Function" = Table.AddColumn(#"Changed Type", "fnBought", each fnBought([PageNumber])),
#"Expanded fnBought" = Table.ExpandTableColumn(#"Invoked Custom Function", "fnBought", {"id", "item_id", "price", "quantity", "created", "purchased"}, {"id", "item_id", "price", "quantity", "created", "purchased"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded fnBought",{{"created", type datetime}, {"purchased", type datetime}}),
#"Removed Duplicates" = Table.Distinct(#"Changed Type1", {"item_id", "price", "quantity", "created", "purchased"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Removed Duplicates",{{"quantity", Int64.Type}, {"price", Int64.Type}, {"item_id", Int64.Type}, {"id", Int64.Type}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type2",{"PageNumber"})
in
#"Removed Columns"
Utilmately this becomes part of a slefsourcing query so I can maintain a longer history.
The excel version of the self sourcing query looks like this (have not converted it into my PowerBI Project yet):
let
Source = Excel.CurrentWorkbook(){[Name="Bought_History"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"id", Int64.Type}, {"item_id", Int64.Type}, {"name", type text}, {"type", type text}, {"level", Int64.Type}, {"rarity", type text}, {"price", Int64.Type}, {"quantity", Int64.Type}, {"created", type datetime}, {"purchased", type datetime}}),
#"Appended Query" = Table.Combine({#"Changed Type", Bought}),
#"Removed Duplicates" = Table.Distinct(#"Appended Query"),
#"Changed Type1" = Table.TransformColumnTypes(#"Removed Duplicates",{{"level", Int64.Type}, {"quantity", Int64.Type}})
in
#"Changed Type1"
Hopefully I have shared enough....as soon as I convert the self sourcing part to PowerBI I will update....If anyone has any suggestions or ideas to make this better than just arbitraritly grabbing 100 pages I would appreciate it.
@Danhouston , Based on what I got, refer if this can help
API pagination
http://sqlcodespace.blogspot.com/2017/09/power-bipower-query-api-response.html
https://datachant.com/2016/06/27/cursor-based-pagination-power-query/
https://medium.com/@marktiedemann/how-to-do-pagination-in-power-query-430460c17c78
Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!
User | Count |
---|---|
119 | |
76 | |
66 | |
51 | |
49 |
User | Count |
---|---|
183 | |
101 | |
80 | |
79 | |
77 |