cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Danhouston
New Member

Power Query for multiple page while checking a date field

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.

 

1 REPLY 1

Helpful resources

Announcements
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

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!

MPPC 2023 PBI Carousel

Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors