Microsoft Dynamics CRM (Archived)

Power BI iteration

(0) Share

Report

Posted on by Community Member Microsoft Employee

In power BI, I would like to iterate a list of values which are returned by a web service as Json object.

I have the query as follow:

let

Source = Json.Document(Web.Contents("xyz.api.crm5.dynamics.com/.../incidents")),

value = Source[value],
count = List.Count(value),
value1 = value{0},
value2 = value1[NRIC_x002e_governmentid]

in
value2

---

The web service will return list of data, how can I loop thru the data, currently I put a "0" in the index for value1=value{0} to get the 1st value, however if there're a lot of data, how can I iterate the data to retrieve all the values.

Please advise, thanks.

*This post is locked for comments

All responses (12)

Answers (1)

Suggested answer

Nithya Gopinath 17,074 on at

Like (0)

Report

RE: Power BI iteration

Hi Kentan,

See the URLs below.

community.powerbi.com/.../135980

blogs.adatis.co.uk/.../Loop-through-Multiple-Web-Pages-using-Power-Query

These links describe how to use the dynamic data to be used in the URLs in Power BI and how to get the data from them.
Community Member Microsoft Employee on at

Like (0)

Report

RE: Power BI iteration

I have another question, now I have the NRIC_x002e_governmentid values, how can I reference these values in another query:

let

Source = Json.Document(Web.Contents("xzy.test.org.sg/.../getdetails; & xyz)),

getmemberdetailsResult = Source[getmemberdetailsResult]

in

getmemberdetailsResult

----

I want to use this "NRIC_x002e_governmentid" as a querystring of a web service (xyz is the query string) and loop thru the "NRIC_x002e_governmentid" values, is it possible to do so.

Thanks in advance!
Nithya Gopinath 17,074 on at

Like (0)

Report

RE: Power BI iteration

Hi Kentan,

If you got the answer, please close the thread as verified so that it can be helpful for others too.
Suggested answer

Community Member Microsoft Employee on at

Like (0)

Report

RE: Power BI iteration

Hi,

Great! I got the value I want.

Thanks for the advice.
Nithya Gopinath 17,074 on at

Like (0)

Report

RE: Power BI iteration

Hi Kentan,

Please refer my above thread. It clearly explains how to convert the list to a table and get the column of that table.
Community Member Microsoft Employee on at

Like (0)

Report

RE: Power BI iteration

Hi,

Thanks for helping, however I got the following error:

Any clues? Thanks.
Verified answer

Nithya Gopinath 17,074 on at

Like (0)

Report
RE: Power BI iteration

Hi Kentan,

You could do the same in the UI.

Right click on the List heading in yellow colour. Select To Table option in that dropdown.

A window appears as follows. Click OK.

A new table of records will be displayed as shown below. Click on the icon marked in red.

In the dropdown that comes, select the column NRIC_x002e_governmentid.

Now you will get the list of all NRIC_x002e_governmentids of all the records.

You could also use the code below.

let Source = Json.Document(Web.Contents("xyz.api.crm5.dynamics.com/.../incidents")), value = Source[value], #"Converted to Table" = Table.FromList(value , Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"NRIC_x002e_governmentid"}, {"Column1.NRIC_x002e_governmentid"}) in #"Expanded Column1"

Hope this helps.
Suggested answer

Nithya Gopinath 17,074 on at

Like (0)

Report
RE: Power BI iteration

Hi,

Try the below code.

let Source = Json.Document(Web.Contents("xyz.api.crm5.dynamics.com/.../incidents")), value = Source[value], count = List.Count(value), value1 = List.FirstN(value, count) value2 = value1({0.. count})[NRIC_x002e_governmentid] in value2

Hope this helps.
Community Member Microsoft Employee on at

Like (0)

Report

RE: Power BI iteration

Hi,

I got a list as shown,

And when I click the record, I get a list of fields data

I only want the NRIC_x002e_governmentid.

Please advise, thanks.
Suggested answer

Nithya Gopinath 17,074 on at

Like (0)

Report
RE: Power BI iteration

Hi Kentan,

Please share the result you are getting when using the query below.

let Source = Json.Document(Web.Contents("xyz.api.crm5.dynamics.com/.../incidents")), value = Source[value], count = List.Count(value), value1 = List.FirstN(value, count) in value1

Hope this helps.