Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
bimmylee
Frequent Visitor

List Parameter only passes Current Value

I am trying to retrieve detail records for every six-digit ID of a list. I have a parameter that is fed this list:

bimmylee_1-1675285069328.png

 

 

I want the parameter to pass the correct value when its function (API call) is invoked for each row of a table (which should be based on the matching row ID of the table) to pull those detail records:

bimmylee_2-1675285230948.png

 

However, it only ever passes the "Current Value" to every row instead of the correct value (the matching one from the CampaignID column). If I uncheck the "Required" box and leave Current Value blank, I get an error about not being able to convert null into text. How can I get around the Current Value requirement so that it dynamically picks the appropriate value based on the row and retrieves the detail records for that row? What is the correct solution here?

 

Edit: For further context, here is the function I am invoking in the custom column:

() => let
        Source = Xml.Tables(Web.Contents(https://webapi.urlexample.com/webapi/v01.0/eobjects/XMLData/ & "CampaignOpenClickDetails/" & Number.ToText(CampaignIdParameter) & "?pageon=1&pagesize=500", [Headers=[#" UserKey”=”key”,#" Company"="company", #"UserName"="user"]])),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"ErrorCount", Int64.Type}, {"SuccessCount", Int64.Type}, {"TotalQueryCount", Int64.Type}}),
        ResultMessages = #"Changed Type"{0}[ResultMessages],
        Campaign = ResultMessages{0}[Campaign],
        Recipients = Campaign{0}[Recipients],
        MailingList = Recipients{0}[MailingList],
        MailingListSubscriber = MailingList{0}[MailingListSubscriber]
    in
        MailingListSubscriber
1 ACCEPTED SOLUTION
watkinnc
Super User
Super User

Do you need to create a parameter for this? I'm not at my desk, but can't you just replace

 

& Number.ToText(CampaignIDParameter)

 

with

 

& each Number.ToText([CampaignID])

 

--Nate


I’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will work!!

View solution in original post

4 REPLIES 4
AlexisOlson
Super User
Super User

@watkinnc is correct. You should pass the ID value from the row, not a single parameter value.

 

A parameter is designed to be constant throughout the query. It's not a variable that gets updated for each row.

watkinnc
Super User
Super User

Do you need to create a parameter for this? I'm not at my desk, but can't you just replace

 

& Number.ToText(CampaignIDParameter)

 

with

 

& each Number.ToText([CampaignID])

 

--Nate


I’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will work!!

Hello @watkinnc and @AlexisOlson ,

Thanks very much for your replies. For some reason I thought a parameter was necessary here but I'm glad to be corrected.
I tried replacing the parameter call in this function with the value ID from the row as Nate suggested but was hit with

Expression.SyntaxError: Token Literal Expected

 at the "each" keyword, so I wrapped it in parenthesis:

() => let
        Source = Xml.Tables(Web.Contents("https://webapi.urlexample.com/webapi/v01.0/eobjects/XMLData/" & "CampaignOpenClickDetails/" & (each Number.ToText([CampaignId]) & "?pageon=1....

 and was then hit with

An error occurred in the 'Campaign Data function' query. Expression.Error: We cannot apply operator & to types Text and Function.
Details:
Operator-&
Left=https://webapi.urlexample.com/webapi/v0.1.0/eobjects/XMLData/CampaignOpenClickDetails/
Right=[Function]

 I'm sure there is some silly syntactical mistake I'm overlooking here but I'm not well-versed enough in M to recognize it.

I figured it out. I was trying to add & each Number.ToText([CampaignID]) to the function when really I needed to scrap the function altogether and just put it into the custom column query itself. Also, Power BI didn't like me using Number.ToText but when I changed the CampaignID column data type to Text pre-emptively and THEN referenced those values in the custom column, it worked. Thank you @watkinnc and @AlexisOlson for sending me down the right path!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors