Deciphering SQL to Power Query Steps

[Babycakes_00]

Hi all

I am unfamiliar with SQL script and have a set of data where the following has been used "

@row_number := CASE

            WHEN @prev_id = t1.id THEN @row_number + 1

 

1. Add an Index Column:

   • Go to the “Add Column” tab.
   • Select “Index Column” and choose “From 1”.

2. Add a Custom Column for Row Number:

   • Add a custom column to calculate the row number for each ID:

     = Table.AddColumn(PreviousStep, "RowNumber", each if [ID] = try PreviousStep{[Index]-2}[ID] otherwise null then [Index] - try PreviousStep{[Index]-2}[Index] otherwise 1)

 

1. Group by ID and Calculate Max Count:
   • Group the table by ID and calculate the maximum row number for each ID:

     = Table.Group(PreviousStep, {"ID"}, {{"MaxCount", each List.Max([RowNumber]), type number}})

 

1. Join the Aggregated Data Back to the Main Query:
   • Join the main query with the aggregated data on ID:

     = Table.Join(MainQuery, "ID", AggregatedData, "ID")

 

1. Calculate Final Minutes:
   • Add a custom column to calculate the final Minutes:

     = Table.AddColumn(PreviousStep, "FinalMinutes", each [Minutes] / [MaxCount])

[lbendlin]

@row_number := CASE

            WHEN @prev_id = t1.id THEN @row_number + 1

Please provide the entire SQL - missing the ELSE and END statements.

[Babycakes_00]

AS max_counts ON J1.ID = max_counts.ID;

[lbendlin]

That is some rather advanced SQL.  Can you confirm that this runs in SSMS?

I would not be surprised if things like 

...

@row_number := CASE

                WHEN @prev_id = t1.id THEN @row_number + 1

                ELSE 1

            END AS `COUNT`,

            @prev_id := t1.id

...

would not be supported in Power Query's SQL connector (unless you declare it as Value.NativeQuery maybe).