Answered

How to retrieve multiple values (delimited) from one cel in excel and use the for PowerBI Report Builder parameter

Like (0) Share

Report

Posted on 28 Aug 2023 09:23:15 by Bob1212

I'm trying to build a flow that extracts values from a excel cel that's the bases for a Prompt in a PowerBI paginated report.

In excel the column CustomerNumber holds values like:

To handle the MULTIPLE value prompt in PowerBI Report Builder
The output should be:

[
 {
 "name": "CustomerNbr",
 "value": "1050"
 },
 {
 "name": "CustomerNbr",
 "value": "1051"
 },
]

How to achieve this in Power Automate?

I have the same question (0)

All responses (11)

Answers (2)

Verified answer

Bob1212 25 on 30 Aug 2023 at 06:50:23

Like (1)

Report

Re: How to retrieve multiple values (delimited) from one cel in excel and use the for PowerBI Report Builder parameter

Yes! this did the trick!
Report is now working and exports an excel ...and also for MULTIPLE prompt values like specified in the printscreen at the start of this topic.

many many thanks @efialttes I will mark it as 'solution'

Was this reply helpful? Yes No
Verified answer

efialttes 14,756 on 29 Aug 2023 at 17:23:50

Like (0)

Report

Re: How to retrieve multiple values (delimited) from one cel in excel and use the for PowerBI Report Builder parameter

@Bob1212 I do not have 'Power BI' license so I cannot use the connector, but from the screenshot you shared I would suggest you the following:
Click on the 'square with a T inside' icon on the top right corner, delete whatever value you see filled inside this input and assign 'Select' action block output to it, so it should look like the following:

Hope this helps

Was this reply helpful? Yes No
Bob1212 25 on 29 Aug 2023 at 14:58:47

Like (0)

Report

Re: How to retrieve multiple values (delimited) from one cel in excel and use the for PowerBI Report Builder parameter

The only Expression in the 'Select action is this Split() - like you suggested -:
split(item()?['DealerNumberSource'],';')

Was this reply helpful? Yes No
efialttes 14,756 on 29 Aug 2023 at 14:39:40

Like (0)

Report

Re: How to retrieve multiple values (delimited) from one cel in excel and use the for PowerBI Report Builder parameter

@Bob1212 On your Flow editor, please expand the Select action block and share a screenshot, please also type any possible WDL expression you are currently using as input
Thanx!

Was this reply helpful? Yes No
Bob1212 25 on 29 Aug 2023 at 14:36:45

Like (0)

Report

Re: How to retrieve multiple values (delimited) from one cel in excel and use the for PowerBI Report Builder parameter

The body() function results in an error at the PowerBI Prompt action.

The reason it's now somehow nested in the default set-up of this activity:
See the red box... I need the green one

Was this reply helpful? Yes No
efialttes 14,756 on 29 Aug 2023 at 13:05:56

Like (0)

Report
Re: How to retrieve multiple values (delimited) from one cel in excel and use the for PowerBI Report Builder parameter

Hi!
I believe WDL expression referencing your Select output is:
body('Select_CustomerNbr_Prompt')
Please note in every iteration of your loop the output is of type array, with one element of type object, as per the example you provided
Hope this helps

Was this reply helpful? Yes No
Bob1212 25 on 29 Aug 2023 at 12:43:07

Like (0)

Report

Re: How to retrieve multiple values (delimited) from one cel in excel and use the for PowerBI Report Builder parameter

Hi @efialttes ,

That worked! I can now see the flow is giving me back the results of the prompt values like desired.

But how can I pass them to the next step: Export To File for Paginated Reports?

in that 'Action' there is a field where the result should be populated... But I can't seem to figure out how to pick the results in our 'Select' action...

Was this reply helpful? Yes No
efialttes 14,756 on 28 Aug 2023 at 15:53:17

Like (0)

Report
Re: How to retrieve multiple values (delimited) from one cel in excel and use the for PowerBI Report Builder parameter

Hi again!
The first problem is, since you are adding Select inside an 'apply to each', you need to reference the current row processed with WDL expression item(). In your case, you wanna evaluate column CustomerNumber, this means you need to reference it by means of item()?['CustomerNumber']. So your split() based function should be as follows:

split(item()?['CustomerNumber'],';')

Now I have this test table...
and I've executed the following flow successfully (3 rows, 3 'Apply to each' iterations). Even if the third row does not have any value assined to column 'customer Number' the test flow does not crash.
If you need such rows to be removed from the execution you can use a Filter Array action block before the 'Apply to each'.
Hope this helps

Was this reply helpful? Yes No
efialttes 14,756 on 28 Aug 2023 at 15:34:36

Like (0)

Report

Re: How to retrieve multiple values (delimited) from one cel in excel and use the for PowerBI Report Builder parameter

Hi @Bob1212 !
Replicating your flow and create a sample excel file, I'll be back in some minutes

Was this reply helpful? Yes No
Bob1212 25 on 28 Aug 2023 at 14:16:08

Like (0)

Report

Re: How to retrieve multiple values (delimited) from one cel in excel and use the for PowerBI Report Builder parameter

Hi
Thanks for your quick reply.
I've added your Select step in my flow:

But it immediately errors (see below)

The Split() function syntax:
split(outputs('List_rows_present_in_a_table')?['CustomerNumber'],';')
is this correct??

The Item() function is kept blank

@efialttes

Was this reply helpful? Yes No