Easily editable lookup table similar to Excel

[ns29]

I'm using a combination of a manually edited "lookup table" in Excel along with Power Pivot to create a file for each customer at time of sale. I want the lookup table to be easily editable by novice users so we can change the values easily and reflect the results quickly. The numbers in the lookup table are unique for each customer. Having an external Excel table would defeat the purpose of each customer file being self-contained. 

Is there a similar way to do this in Power BI. I know I can manually enter a table in Power BI but it is far from being easily editable. In Excel, I just edit the values in the table and refresh the table's connection to the data model and every updates from there automatically. It's almost like I'm looking for a table of parameters. 

Excel sample file here: https://drive.google.com/file/d/1lXxcqaKDnauRYw7efGENgl3MicRNs0qu/view?usp=sharing

Screenshots below.

Thanks!

Lookup tableRelationshipCalculation

[v-easonf-msft]

Hi , @ns29 

Not very clear what you want.

Please think about whether it  can  be solved by using  Power BI Template.

If help ,please refer to this article.

Here is the demo.

sample pbit file attached

For 3rd party  related video Links:

https://www.youtube.com/watch?v=lZXhtyszO-8

Best Regards,
Community Support Team _ Eason

[amitchandak]

@ns29 , same data model and formula should work in power bi, seem like Dax formula.

[ns29]

HI @amitchandak, thanks for trying to help, unfortunately, that wasn't the question I was asking. I'm looking for a solution to easily edit the lookup table. Hopefully you can think of something!

[vivran22]

Hello @ns29,

You can maintain similar table in Power BI where you can edit them using Power Query, if you do not want to maintain a separate excel table for such scenarios.

Cheers!
Vivek

If it helps, please mark it as a solution
Kudos would be a cherry on the top 🙂

https://www.vivran.in/

Connect on LinkedIn

[ns29]

Hi @vivran22 , I do get the fact that I can edit through Power Query, however, that's not a solution for end users. Especially if I publish it and they only have the ability to change the values of parameters. Any additonal thoughts?

[Anonymous · Accepted solution]

Hi @ns29 

As far as I know, and probably you also know, that within Power BI, you can edit the contents of a table in two ways...

1) Using the "Enter Data" option

2) Creating a calculated table using the DAX formula

TableName = 
DATATABLE(
<fieldname>, <datatype>,
<fieldname2>,<datatype>,
...
<fieldnameN>,<datatype>,
   {
     {<value1>,<value2>,...,<valueN>},
     {<value1>,<value2>,...,<valueN>},
     ....
     ....
     {<value1>,<value2>,...,<valueN>}
   }
)

But, in both cases, though it is editable, it has to be done from within PowerBI Desktop or with similar access if the end-users can download the pbix, edit it and publish the edited content again.

But if you are looking at a solution where the editability is part of the user-level access, then you have to do a workaround using PowerApps.  You need to develop a simple PowerApp which can view/edit the contents of the lookup table from your original source and then embed the PowerApp within your PowerBI report using the PowerApp visual. Hopefully, it will work. 

[Anonymous]

Hi @ns29 

Did the PowerApp concept work? Eager to know. If it did, please post how you did it so that your post will be helpful to others who are looking at a similar solution.

[ns29]

@Anonymous that is an interesting idea. Any resources you can point me to or simple templates to get me started?

Thanks!

[Anonymous]

Visit this link 

https://powerapps.microsoft.com/en-us/ 

Microsoft PowerApps for Beginners - Build your first App (Tutorial) 

Hopefully, you will get more help on this from the respective forum of PowerApps.

[vivran22]

Hello @ns29 

I do not think that there are any such options available within Power BI Desktop.

You may submit the idea in the forum:

https://community.powerbi.com/t5/Ideas/ct-p/PBI_Comm_Ideas

Cheers!
Vivek

If it helps, please mark it as a solution
Kudos would be a cherry on the top 🙂

https://www.vivran.in/

Connect on LinkedIn