How can I add my own data to an existing table?

[ianwuk]

I have an existing query that gets data from a database on our server.  How can I add additional columns to that query that are not in the database that I can fill in manually myself?

Thanks.

ianwuk

[Phil_Seamark · Accepted solution]

Hi @ianwuk

Yes you can.  Can manually enter data using the 'Enter Data' feature on the toolbar.  You can then use the Merge table feature in the Query Editor to attach the new column.  

Might pay to have at least one 'key' column that is common to both tables to make joining easier.

[v-yuezhe-msft · Accepted solution]

@ianwuk,

Add conditional column and custom columns as shown in the following screenshot.



Regards,
Lydia

[rockie12_us]

I have looked through the solutions, but when I publish my report to my on prem website, there is no enter data option in the tool bar.

How do I make this available for end users to enter data into the additional comments column I added to my table?

[Phil_Seamark · Accepted solution]

Hi @ianwuk

Yes you can.  Can manually enter data using the 'Enter Data' feature on the toolbar.  You can then use the Merge table feature in the Query Editor to attach the new column.  

Might pay to have at least one 'key' column that is common to both tables to make joining easier.

[ianwuk]

Hello.

Thanks for replying. I will try that.

Is it also possible to add a column that has a drop-down list?

The idea being that based on the value of the drop-down (e.g. Gold, Silver, Bronze) it works out the commission differently for the commission column value in my table.

Hope that makes sense.

ianwuk

[Phil_Seamark]

Not so much in Power BI.

You can type in values but not a dropdown

[ianwuk]

Hello.

I followed your instructions by entering data manually to make a new table.  I then merged that table with the main table.  But how can I then manually update just the parts that I added manually?  I don't seem to be able to figure out how to do that.

Thanks.

[ianwuk]

Hi Phil.

I have an issue now please.

I created the data manually that I want to append to my main table.

The main table has ten records in it so the manual data also has ten records in it (the new columns and data to append).

When I append the manual table to the main one the end result of the main table is that each of those original ten records in the main table is duplicated ten times with each record from the manual table.  It isn't just a 1:1 append - does that make sense?

What am I doing wrong?

[Phil_Seamark]

Did you try the Merge option rather than the Append option?

[ianwuk]

Hello.

Yes,  I fixed it now.  My last part is to create to conditional columns called Company Share and Commission that is conditional to calculate the commission earned on each sale.

For example, if the commission rate value is Bronze (so the value is 0.25 for 25%) then the company share column would be calculated as:

commission rate * list price = company share

The commission rate column is then calculated as:

list price - company share.

For example,

If a product is sold for $100 and the commission rate is 25% then the company share is 0.25 * 100 = 25.

The commission earned is then 100 - 75 = $75.

How can I calculate this in additional custom columns please?

The rate has four unique values:

Gold - 5% (0.05)
Silver - 15% (0.15)
Bronze - 25% (0.25)
Other - 75% (0.75)

I know I need to put this in to some sort of formula but I am not sure how.

Many thanks.

[v-yuezhe-msft · Accepted solution]

@ianwuk,

Add conditional column and custom columns as shown in the following screenshot.



Regards,
Lydia

[ianwuk]

Hello Lydia.

This is great!  Thank you.  However, can I add two conditions to the conditional column?

E.g. IF category = Bronze AND status = Active -> Rate = 0.75

       OR IF category = Bronze AND status = Inactive -> Rate = 0.5

IF category = Silver AND status = Active -> Rate = 0.5

       OR IF category = Silver AND status = Inactive -> Rate = 0.25

And so on?

Many thanks.

[v-yuezhe-msft · Accepted solution]

@ianwuk,

Create a custom column instead.

if [category]="Bronze" and [Status]="Active" then 0.75 
else if [category]="Bronze" and [Status]="inactive" then 0.5 
else if [category]="Sliver" and [Status]="Active" then 0.5 
else if [category]="Sliver" and [Status]="inactive" then 0.25 
else if [category]="Gold" and [Status]="Active" then 0.05 
else if [category]="Gold" and [Status]="inactive" then 0.5 
else if [category]="Other" and [Status]="Active" then 0.75 
else if [category]="Other" and [Status]="inactive" then 0.25
else 0

Regards,
Lydia

[ianwuk]

Thank you so much @v-yuezhe-msft and @Phil_Seamark for all your help.

I think I have it all working now.

Currently the database that PowerBI connects to to get the main data is set to only import the current month.  I believe this was set up in Power BI itself.  If I wanted to get year to date(from 1st January 2018 - now) in my new merged table instead, which starts at 1st February 2018,  (this is the one I merged and added all the custom columns too), how could I do that? 

The idea being that I can set that up as the query date range (year to date) and just filter the data by month in report view.  Is that possible?

[ianwuk]

Hello @v-yuezhe-msft. I have one more request please.

How can I edit the query for this custom column:

[List Price] = [Collected Price]

To make it so that, for order 12345, the Collected Price is actually 414.12 intead of the list price value?

 
What am I doing wrong?

Thanks.