Forum Discussion

dday9's avatar
dday9
Frequent Visitor
7 years ago
Solved

Converting ColumnsUpdated Field

In my SQL datatable I have a ColumnsUpdated field which is a VARBINARY representation of the COLUMNS_UPDATED T-SQL function. I have a custom scalar-valued function which converts the column by essent...
Need Help
  • dday9's avatar
    dday9
    7 years ago

    Greg_DecklerI was able to solve this issue by created a CTE in the Advanced Options of Get Data, setting up the relationship, and then displaying the parsed ColumnsUpdated from the CTE in a report.

     

    The SQL I ran during import was:

    WITH ChangeLog_CTE (Guid, ParsedUpdatedColumns)  
AS
(  
    SELECT
        Guid,
        (dbo.[GetChangedColumns]('History.Resources', History.Resources.ColumnsUpdated)) AS ParsedUpdatedColumns
    FROM
        History.Resources
)
SELECT *  
FROM ChangeLog_CTE;

     

Greg_Deckler's avatar
Greg_Deckler
Community Champion
7 years ago

I'm following about half of this. In Power BI you don't generally update existing columns and tables, it is for reporting. 

dday9's avatar
dday9
Frequent Visitor
7 years ago

My apologies, I should have been clearer in my original post.

 

The table that I am referring to is a temporal table and in this table there is a column named ColumnsUpdated. The ColumnsUpdated column is a VARBINARY column that is the result of the COLUMNS_UPDATED function via a trigger. What I'm attempting to do is created a Power BI report in which it displays a delimited list of columns that were updated for a given row (requirement A) as well as the user's name of who updated the row (requirement b).

 

Currently in my database I have a scalar-valued function that returns a delimited list, this would solve requirement A. I understand how to run the function in SQL to return the delimited list, but I don't understand how to run the function in Power BI.