Patch option set or multi-select option set from Power BI to CDS

[mguinan]

Hi,

I have a canvas app with [@PowerBIIntegration].Data set up and I have a couple fields, some of which are option sets and one of which is a multi-select option set. I have a Patch statement written that is working for simple fields (text, lookup, etc.), but I cannot find any resource on how to accomplish patching option sets and/or multi-select option set values from Power BI. I want to do this dynamically (user makes selection > patch with the value), not with hard-coded values. Does anyone have an example or know how I can accomplish this? Thanks in advance

[v-xida-msft]

Hi @mguinan ,

Could you please share a bit more about your scenario?

Do you embed your canvas app into your Power BI Report?

Further, do you want to patch selected values back to the Option Set column or Multi Select Option Set column in your CDS Entity using Patch function?

Based on the issue that you mentioned, I have made a test on my side, please consider try the following workaround:

You could consider add two ComboBox controls inside your app screen. One (ComboBox1) for Option Set field, another (ComboBox2) for Multi-Select Option Set field. Set the SelectMultiple property of the ComboBox1 to false. The screenshot as below:

Set the Items property of the ComboBox1 to following:

Choices(OptionSetName)

Set the Items property of the ComboBox2 to following:

Choices(MultipleSelectOptionSetName)

1) If you want to patch selected Option Set option back to the Option Set field in your Entity, please try the following formula:

Patch(
      TaskLists, 
      LookUp(TaskLists, TaskLists = BrowseGallery1.Selected.TaskLists), 
      {
          ApprovalStatus: ComboBox1.Selected.Value
      }
)

On your side, you may try the following formula:

Patch(
      'Your Entity', 
      LookUp('Your Entity', 'Unique Identifier Column' = First([@PowerBIIntegration].Data).'Unique Identifier Column'), 
      {
          OptionSetFieldNameInEntity: ComboBox1.Selected.Value
      }
)

2) If you want to patch selected Option Set options back to the Multiple Select Option Set field in your Entity, please try the following formula:

Patch(
      TaskLists, 
      LookUp(TaskLists, TaskLists = BrowseGallery1.Selected.TaskLists), 
      {
          'Multiple Job Status': ComboBox2.SelectedItems.Value
      }
)

On your side, you may need to try the following formula:

Patch(
      'Your Entity', 
      LookUp('Your Entity', 'Unique Identifier Column' = First([@PowerBIIntegration].Data).'Unique Identifier Column'), 
      {
          MulSelectOptionSetFieldNameInEntity: ComboBox2.SelectedItems.Value
      }
)

Note: The 'Unique Identifier Column' represents the 'Unique Identifier Column' in your CDS Entity, please replace it with actual column name

Please also check and see if the following blog would help in your scenario:

https://powerapps.microsoft.com/en-us/blog/option-sets-and-many-to-many-relationships-for-canvas-app...

Best regards,

[mguinan]

Hi Kris,

Yes, this is an embedded canvas app in a Power BI Report which will then Patch to create records in CDS.

I think that the option set and multi-select option sets being populated in a combo box is helpful, however, the user would not be selecting these values in the app. The user will be selecting Account records in Power BI which would then have the combo box values as part of the records they selected. For example, user selected ABC Company record. The Industry (option set) field for ABC Company is already set to Construction. I want to pull the Construction value into the app to then use in the Patch statements like you have suggested. Is there a way to map the Construction value from Power BI to the combo boxes behind the scenes? So if a user has selected a record with Construction in Power BI, it is reflected in the combo box? I am hoping I can somehow hide the combo boxes, populate them in the background and then use the Patch statements that you have provided. 

[v-xida-msft · Accepted solution]

Hi @mguinan ,

For your needs, I think PowerApps canvas app could achieve your needs. You could pull the Option Set type column value of the selected record in your Power BI report using the following formula:

LookUp('Your CDS Entity', 'Unique Identifier Column' = First([@PowerBIIntegration].Data).'Unique Identifier Column').OptionSetColumn

If you want to populate the Option Set type column value of the selected record in your Power BI report into the Option Set field ComboBox in your app, please consider set the Items property of the ComboBox (ComboBox1) to following:

Choices('Your CDS Entity'.OptionSetName)

Set the DefaultSelectedItems property of the ComboBox(ComboBox1) to following:

[LookUp('Your CDS Entity', 'Unique Identifier Column' = First([@PowerBIIntegration].Data).'Unique Identifier Column').OptionSetColumn]

If you want to populate the Multi-Select Option Set type column value of the selected record in your Power BI report into the Multi-Select Option Set field ComboBox in your app, please consider set the Items property of the ComboBox (ComboBox2) to following:

Choices(MultiSelectOptionSetName)

Set the DefaultSelectedItems property of the ComboBox (ComboBox2) to following:

LookUp('Your CDS Entity', 'Unique Identifier Column' = First([@PowerBIIntegration].Data).'Unique Identifier Column').MultiSelectOptionSetColumn

in your canvas app, you could set the Visible property of above ComboBox to false, so the two ComboBox would be hidden.

After that, you could try the Patch formula I provided above to update the Option Set field pr Multi-Select Option Set field value.

Best regards,

[mguinan · Accepted solution]

Thank you for your suggestions. I ended up doing the following since I needed to both pull from PowerBI directly as well as what the user selected inside the canvas Power App. Both examples are below for other individuals to use for reference:

/ Clear Temporary Collection
Clear(colOppCreate);
// Build Temporary Collection, map columns to 'app' prefixed, to disambiguate
ForAll(
    [@PowerBIIntegration].Data,
    Collect(
        colOppCreate,
        {
            //value from PowerBI
            appIndustry: Industry,
            //value from inside canvas power app
            appStatusReason: cmbReason.Selected.Value
        }
    )
);

// Patch - create Opportunity records
ForAll(
    colOppCreate,
    Patch(
        Opportunities,
        Defaults(Opportunities),
        {
            Industry: LookUp(
                Choices(Industry),
                Text(Value) = appIndustry
            ).Value,
            'Status Reason': Filter(
                Choices('Opportunity Status Reason'),
                Value = appStatusReason
            )
        }
    )
);

  
Thanks again for all your help!

[v-xida-msft]

Hi @mguinan ,

Have you solved your problem? Is the solution I provided above helpful in your scenario?

If you have solved your original problem, please consider go ahead to click "Accept as Solution" to identify this thread has been solved.

If you still have some other issues with your scenario, please feel free to open a new thread in our community, and we would help you in that new thread.

Best regards,

[v-xida-msft · Accepted solution]

Hi @mguinan ,

Is the solution I provided above helpful in your scenario?

If the solution I provided above is helpful in your scenario, please also consider go ahead to click "Accept as Solution" to identify my reply as helpful.

Regards,