Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
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
  • Anonymous's avatar
    Anonymous
    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
Icon for Community Champion rankCommunity 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. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    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
      Icon for Community Champion rankCommunity Champion
      7 years ago

      Anonymous , glad you got it figured out. Yes, the Advanced Options will let you run whatever SQL Query you desire. Didn't think of that as the answer to your question because I'm still not exactly sure what you are going for!! :)