Microsoft Dynamics CRM (Archived)

Power BI iteration

(0) Share

Report

Posted on by Community Member

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

I have the same question (0)

All responses (12)

Answers (1)

Suggested answer

Nithya Gopinath 17,078 on at

Like (0)

Report
Hi Kentan,

You could use the List.FirstN function to retrieve the same.

List.FirstN(value,count)

See: msdn.microsoft.com/.../mt253620.aspx

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

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[NRIC_x002e_governmentid]

in

value2

---

I got an error :

Expression.Error: We cannot apply field access to the type List.

Details:

Value=List

Key=NRIC_x002e_governmentid

---

I am wondering how come I couldn't getthe specified field: "NRIC_x002e_governmentid"

Any advice? Thanks.

Was this reply helpful? Yes No
Suggested answer

Nithya Gopinath 17,078 on at

Like (0)

Report
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.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Suggested answer

Nithya Gopinath 17,078 on at

Like (0)

Report
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.

Was this reply helpful? Yes No
Verified answer

Nithya Gopinath 17,078 on at

Like (0)

Report
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.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi,

Thanks for helping, however I got the following error:

Any clues? Thanks.

Was this reply helpful? Yes No
Nithya Gopinath 17,078 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Suggested answer

Community Member on at

Like (0)

Report

Hi,

Great! I got the value I want.

Thanks for the advice.

Was this reply helpful? Yes No
Nithya Gopinath 17,078 on at

Like (0)

Report

Hi Kentan,

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

Was this reply helpful? Yes No