topic Re: Looping Sources in Developer

Looping source in PowerBI

dmqeaz — Wed, 17 May 2017 08:49:41 GMT

Hi all,

I'm new to PowerBI, hope to get some help.

I want to build someting around the json webAPI's of MoySklad. There stored demands with links to their positions, that contains links to products. Link for demand's positions looks like this:

https://online.moysklad.ru/api/remap/1.1/entity/demand/{id}/positions

for one Demand code looks like this:

let
    Источник = Json.Document(Web.Contents("https://online.moysklad.ru/api/remap/1.1/entity/demand/ad760bfb-7b5c-11e3-49fe-002590a28eca/positions")),
    rows = Источник[rows],
    #"Преобразовано в таблицу" = Table.FromList(rows, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Развернутый элемент Column1" = Table.ExpandRecordColumn(#"Преобразовано в таблицу", "Column1", {"meta", "id", "accountId", "quantity", "price", "discount", "vat", "assortment", "overhead"}, {"Column1.meta", "Column1.id", "Column1.accountId", "Column1.quantity", "Column1.price", "Column1.discount", "Column1.vat", "Column1.assortment", "Column1.overhead"}),
    #"Развернутый элемент Column1.assortment" = Table.ExpandRecordColumn(#"Развернутый элемент Column1", "Column1.assortment", {"meta"}, {"Column1.assortment.meta"}),
    #"Развернутый элемент Column1.assortment.meta" = Table.ExpandRecordColumn(#"Развернутый элемент Column1.assortment", "Column1.assortment.meta", {"href"}, {"Column1.assortment.meta.href"})
in
    #"Развернутый элемент Column1.assortment.meta"

I want to load all the position for all the demands so I can drill down my analysis to products level.

basically i understand, that i need loop my request with demand's IDs, but is Power BI able to do this?

Thanks in advance,

Dmitry

Looping Sources

dmqeaz — Wed, 17 May 2017 08:45:02 GMT

Hi all,

I'm new to PowerBI, hope to get some help.

I want to build someting around the json webAPI's of MoySklad. There stored demands with links to their positions, that contains links to products. Link for demand's positions looks like this:

https://online.moysklad.ru/api/remap/1.1/entity/demand/{id}/positions

for one Demand code looks like this:

let
    Источник = Json.Document(Web.Contents("https://online.moysklad.ru/api/remap/1.1/entity/demand/ad760bfb-7b5c-11e3-49fe-002590a28eca/positions")),
    rows = Источник[rows],
    #"Преобразовано в таблицу" = Table.FromList(rows, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Развернутый элемент Column1" = Table.ExpandRecordColumn(#"Преобразовано в таблицу", "Column1", {"meta", "id", "accountId", "quantity", "price", "discount", "vat", "assortment", "overhead"}, {"Column1.meta", "Column1.id", "Column1.accountId", "Column1.quantity", "Column1.price", "Column1.discount", "Column1.vat", "Column1.assortment", "Column1.overhead"}),
    #"Развернутый элемент Column1.assortment" = Table.ExpandRecordColumn(#"Развернутый элемент Column1", "Column1.assortment", {"meta"}, {"Column1.assortment.meta"}),
    #"Развернутый элемент Column1.assortment.meta" = Table.ExpandRecordColumn(#"Развернутый элемент Column1.assortment", "Column1.assortment.meta", {"href"}, {"Column1.assortment.meta.href"})
in
    #"Развернутый элемент Column1.assortment.meta"

I want to load all the position for all the demands so I can drill down my analysis to products level.

basically i understand, that i need loop my request with demand's IDs, but is Power BI able to do this?

Thanks in advance,

Dmitry

Re: Looping Sources

Eric_Zhang — Fri, 19 May 2017 03:05:49 GMT

@dmqeaz wrote:

Hi all,

I'm new to PowerBI, hope to get some help.

I want to build someting around the json webAPI's of MoySklad. There stored demands with links to their positions, that contains links to products. Link for demand's positions looks like this:

https://online.moysklad.ru/api/remap/1.1/entity/demand/{id}/positions

for one Demand code looks like this:
let
    Источник = Json.Document(Web.Contents("https://online.moysklad.ru/api/remap/1.1/entity/demand/ad760bfb-7b5c-11e3-49fe-002590a28eca/positions")),
    rows = Источник[rows],
    #"Преобразовано в таблицу" = Table.FromList(rows, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Развернутый элемент Column1" = Table.ExpandRecordColumn(#"Преобразовано в таблицу", "Column1", {"meta", "id", "accountId", "quantity", "price", "discount", "vat", "assortment", "overhead"}, {"Column1.meta", "Column1.id", "Column1.accountId", "Column1.quantity", "Column1.price", "Column1.discount", "Column1.vat", "Column1.assortment", "Column1.overhead"}),
    #"Развернутый элемент Column1.assortment" = Table.ExpandRecordColumn(#"Развернутый элемент Column1", "Column1.assortment", {"meta"}, {"Column1.assortment.meta"}),
    #"Развернутый элемент Column1.assortment.meta" = Table.ExpandRecordColumn(#"Развернутый элемент Column1.assortment", "Column1.assortment.meta", {"href"}, {"Column1.assortment.meta.href"})
in
    #"Развернутый элемент Column1.assortment.meta"
I want to load all the position for all the demands so I can drill down my analysis to products level.

basically i understand, that i need loop my request with demand's IDs, but is Power BI able to do this?

Thanks in advance,

Dmitry

@dmqeaz

For loop, I guess there's a table containing the ids? If yes, you can try to add custom column as below.

Anyway, for your case, I don't think loop in Power BI is a good idea. For me, I'd loop and save the hanlded result by writing a C# console application(can be scheduled to running in a certain interval) in a csv file or database. The connect to the file or DB in Power BI.

Re: Looping Sources

dmqeaz — Fri, 19 May 2017 08:48:26 GMT

@Eric_Zhang

There are links already in this table, but every link gives list of positions of demand in request with id selected.

example:

https://online.moysklad.ru/api/remap/1.1/entity/demand/51a95d05-7901-11e3-9575-002590a28eca/positions

let
    Источник = Json.Document(Web.Contents("https://online.moysklad.ru/api/remap/1.1/entity/demand/51a95d05-7901-11e3-9575-002590a28eca/positions")),
    rows = Источник[rows],
    #"Преобразовано в таблицу" = Table.FromList(rows, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Развернутый элемент Column1" = Table.ExpandRecordColumn(#"Преобразовано в таблицу", "Column1", {"meta", "id", "accountId", "quantity", "price", "discount", "vat", "assortment", "overhead"}, {"Column1.meta", "Column1.id", "Column1.accountId", "Column1.quantity", "Column1.price", "Column1.discount", "Column1.vat", "Column1.assortment", "Column1.overhead"})
in
    #"Развернутый элемент Column1"

and it gives me list of postions for 51a95d05-7901-11e3-9575-002590a28eca demand

I need to load table with list of positions of all demands concatenated like:

for each demand id

concatenate

load https://online.moysklad.ru/api/remap/1.1/entity/demand/{id}/positions

and get single table as a result