My Digital Life in Data Flow

1. New PowerBI file >> Get Data
2. Set up our SQL connection.  Because JSON parsing function is not supported natively, this connection cannot be Direct Query.  Even if we set it up as such, later steps will force PowerBI to change the connection type to "Import".
   Do not click "Load", click "Edit" to get into Query Designer
3. In PowerQuery Editor, right-click on the column we want to parse JSON, select "Transform" >> "JSON"
   
4. A new "expand" button will appear on the top right corner of column header.  Click on that and select the fields out of JSON we want to report on.  Then click "OK"
   
5. When we are done with the dataset, "Close and Apply"
6. The JSON fields are now available to report on.
   

1. Modify the existing table, add a column and populate with unique key data, then delete off that unique key, or
2. SELECT DISTINCT * INTO [a temp table] FROM [current table], TRUNCATE FROM [current table], INSERT INTO [current table] SELECT * FROM [a temp table]

-- test delete duplicate rows from table without changing table

CREATE TABLE dbo.DeleteDupFromViewTest

       (FirstName VARCHAR(100) NOT NULL

       , LastName VARCHAR(100) NOT NULL)

GO

INSERT INTO dbo.DeleteDupFromViewTest

              (FirstName, LastName)

       VALUES

              ('FirstNameOne', 'LastNameOne')

              , ('FirstNameTwo', 'LastNameTwo')

              , ('FirstNameThree', 'LastNameThree')

              , ('FirstNameThree', 'LastNameThree')

              , ('FirstNameThree', 'LastNameThree')

              , ('FirstNameOne', 'LastNameOne')

              , ('FirstNameTwo', 'LastNameTwo')

              , ('FirstNameTwo', 'LastNameTwo')

              , ('FirstNameThree', 'LastNameThree')

              , ('FirstNameThree', 'LastNameThree')

              , ('FirstNameOne', 'LastNameOne')

GO

CREATE VIEW dbo.vw_DeleteDupFromViewTest AS

       SELECT

              FirstName

              , LastName

              , ROW_NUMBER() OVER(ORDER BY FirstName, LastName) AS RowID

       FROM dbo.DeleteDupFromViewTest

GO

SELECT * FROM dbo.vw_DeleteDupFromViewTest

DELETE t1

       FROM dbo.vw_DeleteDupFromViewTest t1

              LEFT OUTER JOIN (SELECT FirstName

                                                , LastName

                                                , MIN(RowID) AS RowID

                                         FROM dbo.vw_DeleteDupFromViewTest (NOLOCK)

                                         GROUP BY FirstName

                                                , LastName

              ) t2

                     ON t1.FirstName = t2.FirstName

                           AND t1.LastName = t2.LastName

                           AND t1.RowID = t2.RowID

       WHERE t2.FirstName IS NULL

SELECT * FROM dbo.vw_DeleteDupFromViewTest

SELECT * FROM dbo.DeleteDupFromViewTest

DROP TABLE dbo.DeleteDupFromViewTest

Screenshot of result: