Hello,
I want to test the prediction model in the AI builder by using my own data, which is an excel file. In order to do that I have created an entity by pressing:
and there I have chosen to load into a new entity. When I am about to chose the data type for the fields I only have two options: "text" and "multiline text". But what I want is to choose "two options" since I am going to use the prediction model. I know that once a field is saved I can’t change the data type except from “text” to “autonumber”.
So I tried to delete the entity and get it again. But I do not see where I am supposed to set the data type as “two options” while I am in the process of getting the data (the excel file). I can't see any option for selecting “two options”…
Do you have any idea how I can fix it?
Best regards,
Ina
@Anonymous
We can only choose "text" and "multiline text" type in power query, this is by design.
My workaround is to generate the two options field later in the PowerApps, so you first import excel to text field, and then creat a two option set field in entity.
Then in the PowerApps, convert the text field to Two options set field in bulk.
Take Account entity as an example, I will convert M_Text column to TwoOption column.
1. create a collection name col1, to save the Account ID and imported Text column
ClearCollect(Col1,RenameColumns(ShowColumns(Accounts,"crf0d_m_text","accountid"),"crf0d_m_text","MT1","accountid","AID"))
2. use ForAll function to iterate each collection record, and patch value to TwoOption column.
ForAll(Col1,If(MT1="Yes",Patch(Accounts,LookUp(Accounts,Account=AID),{TwoOption:'TwoOption (Accounts)'.Yes}),MT1="No",Patch(Accounts,LookUp(Accounts,Account=AID),{TwoOption:'TwoOption (Accounts)'.No})))
Sik
@Anonymous
We can only choose "text" and "multiline text" type in power query, this is by design.
My workaround is to generate the two options field later in the PowerApps, so you first import excel to text field, and then creat a two option set field in entity.
Then in the PowerApps, convert the text field to Two options set field in bulk.
Take Account entity as an example, I will convert M_Text column to TwoOption column.
1. create a collection name col1, to save the Account ID and imported Text column
ClearCollect(Col1,RenameColumns(ShowColumns(Accounts,"crf0d_m_text","accountid"),"crf0d_m_text","MT1","accountid","AID"))
2. use ForAll function to iterate each collection record, and patch value to TwoOption column.
ForAll(Col1,If(MT1="Yes",Patch(Accounts,LookUp(Accounts,Account=AID),{TwoOption:'TwoOption (Accounts)'.Yes}),MT1="No",Patch(Accounts,LookUp(Accounts,Account=AID),{TwoOption:'TwoOption (Accounts)'.No})))
Sik
Thank you a lot for the answer!
If I understand you correctly, I need to import the excel data in power query and thereafter I'll make kind of "an extra" field that is two option field. Then I am little bit lost... M_Text is a field within the Account entity in your case and I am wondering where you are located when you convert the field from being text to being two option? (do you make new App for it, and if so where can you put these functions in)
Best regards,
Ina
@Anonymous
Yes, I create a PowerApps to execute the above codes to convert the Text field(M_Text) to Two Option(TwoOption).
1. Create an app, connect to the entity where you import the excel file.
2. Add a button, paste the above two codes to OnSelect property of button as below
ClearCollect(Col1,RenameColumns(ShowColumns(Accounts,"crf0d_m_text","accountid"),"crf0d_m_text","MT1","accountid","AID"));
ForAll(Col1,If(MT1="Yes",Patch(Accounts,LookUp(Accounts,Account=AID),{TwoOption:'TwoOption (Accounts)'.Yes}),MT1="No",Patch(Accounts,LookUp(Accounts,Account=AID),{TwoOption:'TwoOption (Accounts)'.No})))
3. You need to change the entity name and column name to yours, e.g. YourEntity, TextFiedName, EntityGUID, TwoOptionFieldName.
ClearCollect(Col1,RenameColumns(ShowColumns('YourEntity',"TextFieldName","EntityGUID"),"TextFieldName","MT1","EntityGUID","AID"));
ForAll(Col1,If(MT1="Yes",Patch('YourEntity',LookUp('YourEntity','EntityGUID'=AID),{'TwoOptionFieldName':'TwoOptionFieldName (YourEntity)'.Yes}),MT1="No",Patch('YourEntity',LookUp('YourEntity','EntityGUID'=AID),{'TwoOptionFieldName':'TwoOptionFieldName (YourEntity)'.No})))
Please let me know if this doesn't work.
Sik
Hi Sik,
Thank you for the effort you have put into helping me! I would like to send you a screenshot of how it is in my computer and the error that I am getting since I have no idea of how to fix it. I would appreciate it a lot if you could help me out.
The entity name is: cr4d9_hotelcancellation
The text field name is: cr4d9_revenue
The entity GUID is: cr4d9_hotelcancellationid
The two option field name is: cr4d9_prufa
Best regards,
Ina
@Anonymous
Please use Entity name of entity and Display Name of field.
I guess your entity name is 'Hotel Cancellations' and the Text field display name is Revenue, Two option is Prufa, GIUD field is 'Hotel Cancellation'.
Then your formula should be:
ClearCollect(Col1,RenameColumns(ShowColumns('Hotel Cancellations',"Revenue","Hotel Cancellation"),"Revenue","MT1","Hotel Cancellation","AID"));
ForAll(Col1,If(MT1="Yes",Patch('Hotel Cancellations',LookUp('Hotel Cancellations','Hotel Cancellation'=AID),{Prufa:'Prufa (Hotel Cancellations)'.Yes}),MT1="No",Patch('Hotel Cancellations',LookUp('Hotel Cancellations','Hotel Cancellation'=AID),{Prufa:'Prufa (Hotel Cancellations)'.No})))
Note: The two option Items is Yes and No.
Sik
Hello @v-siky-msft,
So, I am still stuck with the prediction model. I made the app, connected the entity to it and I get errors saying e.g. "The specified column revenue does not exist" which is one of the field that is within the entity and the same goes for the other field "Hotel Cancellation".
Do you have any idea what I am doing wrong or how I can fix it?
Best regards,
Ina
Hello @v-siky-msft,
So, I am still stuck with the prediction model. I made the app, connected the entity to it and I get errors saying e.g. "The specified column revenue does not exist" which is one of the field that is within the entity and the same goes for the other field "Hotel Cancellation".
Do you have any idea what I am doing wrong or how I can fix it?
Best regards,
Ina
The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.
This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.
User | Count |
---|---|
186 | |
53 | |
41 | |
36 | |
30 |
User | Count |
---|---|
240 | |
75 | |
71 | |
69 | |
65 |