cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mguinan
Regular Visitor

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

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

3 ACCEPTED SOLUTIONS

Accepted Solutions

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,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

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!

View solution in original post

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,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

6 REPLIES 6
v-xida-msft
Community Support
Community Support

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:

4.JPG

 

5.JPG

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,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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. 

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,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

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!

View solution in original post

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,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Users online (4,611)