Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Track Power Query value change and show in table visualization

Hello,

I have the following table loaded in Power Query and the data is refreshed from an Excel spreadsheet every day. The problem is that for some Document ID's, the invoice amount is not correct due to missing invoices, so the missing invoiced amount needs to be added manually in the dashboard. So, i would currently add a new step in Power Query. For example:

 

= Table.InsertRows(#"Renamed Columns",List.PositionOf(#"Renamed Columns"[Document ID],4060)+1,{[Document ID=4060, #"Invoice amount"=1584.98]})

 

I am not sure if this is the best approach to this problem but how to track those manual value changes in Power Query and indicate in the table visualization next to the value that it was changed manually? I am thinking maybe using conditional formatting to show an icon next to the value if it was manually edited in Power Query? Is this possible? Any help is much appreciated!

 

Document IDInvoice amount
39982439.17
40501176.56
4050791024.45
40601810
413912352.03
414581045.86
4149545.95
41574513.88
416052011
4160542.4
41649592.56
416687441.67
41876793.62
418733938.1
41874390
4187135385
4248154310
43144598.01
447478597
448194904.73
4487372359.04
44951144
4685734125.54
46884073682
4691175683
481384100
4832437233
49971636831
Need Help
  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    4 years ago

    Hi Anonymous ,

     

    You can use enter data to create a new table and append the original table with the enter data table. Then use the following code to add a icon via conditional formatting.

    Measure = IF(SELECTEDVALUE(manual[Document ID]) in VALUES('Table'[Document ID]),"https://emoji-uc.akamaized.net/orig/c1/d9d88630432cf61ad335df98ce37d6.png","https://emoji-uc.akamaized.net/orig/0d/5232f11b284da4884088de94d0da1d.png")

     

    Result:

    Pbix in the end you can refer.

    Best Regards

    Community Support Team _ chenwu zhu

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • MahyarTF's avatar
    MahyarTF
    Memorable Member
    4 years ago

    Hi,

    Your solution is perfect,

    I think maybe you could use below :

    - You add the Status column to the current table (including 'Existing' or what ...)

    - then create another table for the records that you added manually (that is come from a separate Spreadsheet) and filled the status field with 'Updated' or 'Manually' or ...)

    - Then Append these two tables and use the third column (Status) in your visuals and Dax codes.

     

    Appreciate for Kudos

  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    Community Support
    4 years ago

    Hi Anonymous ,

     

    You can use enter data to create a new table and append the original table with the enter data table. Then use the following code to add a icon via conditional formatting.

    Measure = IF(SELECTEDVALUE(manual[Document ID]) in VALUES('Table'[Document ID]),"https://emoji-uc.akamaized.net/orig/c1/d9d88630432cf61ad335df98ce37d6.png","https://emoji-uc.akamaized.net/orig/0d/5232f11b284da4884088de94d0da1d.png")

     

    Result:

    Pbix in the end you can refer.

    Best Regards

    Community Support Team _ chenwu zhu

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.