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.
