Forum Discussion

DJSwezey's avatar
DJSwezey
Icon for Helper I rankHelper I
2 years ago

Power BI Dashboard as Data Source in Excel w/ added column

I have a Power BI Dataset that I have published with clients who will require a sales campaign (Example).

ClientCampaignClientID
Freds ElectricalSolar15
Joes BBQBurgers25
Randys CleanersDetergent

50

 

Within Excel I have grabbed this dataset from the Power Platform > Power BI option and created a Table.

 

I have then added a new column called "Status" that will be used for salespeople to come into the Excel document and update the status of the campaigns.

ClientCampaignClientIDStatus
Freds ElectricalSolar15Lead
Joes BBQBurgers25Closed
Randys CleanersDetergent50In Progress

 

Here is my issue: If a new client is added or an exising client is removed from the Power BI Dataset and the Excel table is refreshed, the data manually entered into the "Status" column no longer is associated to the original row. 

ClientCampaignClientIDStatus
Danielles DancingEnsemble 75Lead
Joes BBQBurgers25Closed
Randys CleanersDetergent50In Progress

For example, if Freds Eletrical was removed and Danielles Dancing was added, the status "Lead" for Freds Electrical is not removed and stays in the same row, thus making the status for Danielles Dancing incorrect.

 

How can I get this "Status" column in Excel with a manual entry to be associated to the ClientID so if/when data changes the Status can appropriately match the client?

Need Help

1 Reply

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    2 years ago

    Short answer: You  can't.

     

    Long answer:  You would need to find a way to write back from Excel into the Power BI dataset's semantic model's data source. For example with a VBA macro that writes your comments into the SQL Server database table that is the source for Power BI.  Technically possible but quite complex and horrible UX.