Power Query M Code to SQL

[Nigel_Mayhew1]

Hi there,

Does anyone know if there is a platform where one can 'translate' M code into SQL?

I've created a large query in PQ that involves many steps, and also several left outer merges with other tables. All the tables are from the same SQL server.

Since it's best to do all the changes and merges as high up the foodchain as possible, I want to do all the things I've done in M code in the SQL server itself. Then I'll just pull the finished/completed final table from SQL into PQ and save myself the very long wait for the queries to update.

Query folding doesn't work (greyed out) as I've created complicated steps.

Looking forward to your feedback and suggestions.

Many thanks, Nigel

[amustafa]

Hi @Nigel_Mayhew1 I'll do it at $20/hr charge 

Here's a sample:

MCode:

#"Removed Duplicates" = Table.Distinct(#"Filtered Rows"),
#"Added Conditional Column" = Table.AddColumn(#"Removed Duplicates", "HS2", each if Text.StartsWith([Series Display Name], ".") then null else [Series Display Name]),
#"Filled Down" = Table.FillDown(#"Added Conditional Column",{"HS2"}),
#"Renamed Columns" = Table.RenameColumns(#"Filled Down",{{"HS2", "HS2 Series"}}),
#"Filtered Rows1" = Table.SelectRows(#"Renamed Columns", each Text.StartsWith([Series Display Name], ".")),
#"Replaced Value" = Table.ReplaceValue(#"Filtered Rows1",".","",Replacer.ReplaceText,{"Series Display Name"}),
#"Trimmed Text" = Table.TransformColumns(#"Replaced Value",{{"Series Display Name", Text.Trim, type text}}),
#"Merged Queries" = Table.NestedJoin(#"Trimmed Text", {"Series Key"}, #"Export Receipts by all Commodities-HS2 level", {"Series Key"}, "Export Receipts by all Commodities-HS2 level", JoinKind.RightOuter),
#"Expanded Export Receipts by all Commodities-HS2 level" = Table.ExpandTableColumn(#"Merged Queries", "Export Receipts by all Commodities-HS2 level", {"Observation Date", "Observation Value", "Unit"}, {"Export Receipts by all Commodities-HS2 level.Observation Date", "Export Receipts by all Commodities-HS2 level.Observation Value", "Export Receipts by all Commodities-HS2 level.Unit"}),
#"Filtered Rows2" = Table.SelectRows(#"Expanded Export Receipts by all Commodities-HS2 level", each ([HS2 Series] <> null)),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows2",{"Export Receipts by all Commodities-HS2 level.Unit"}),
#"Added Custom" = Table.AddColumn(#"Removed Columns", "Value USD", each [#"Export Receipts by all Commodities-HS2 level.Observation Value"]*1000),
#"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"Export Receipts by all Commodities-HS2 level.Observation Value"}),
#"Renamed Columns1" = Table.RenameColumns(#"Removed Columns1",{{"Export Receipts by all Commodities-HS2 level.Observation Date", "Date"}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns1",{{"Value USD", Currency.Type}}),
#"Trimmed Text1" = Table.TransformColumns(#"Changed Type1",{{"Series Display Name", Text.Trim, type text}}),
#"Duplicated Column" = Table.DuplicateColumn(#"Trimmed Text1", "Series Display Name", "Series Display Name - Copy"),
#"Duplicated Column1" = Table.DuplicateColumn(#"Duplicated Column", "HS2 Series", "HS2 Series - Copy"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Duplicated Column1", "Series Display Name - Copy", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Series Display Name - Copy.1", "Series Display Name - Copy.2"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Series Display Name - Copy.1", Int64.Type}, {"Series Display Name - Copy.2", type text}}),
#"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type2", "HS2 Series - Copy", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"HS2 Series - Copy.1", "HS2 Series - Copy.2"}),
#"Changed Type3" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"HS2 Series - Copy.1", Int64.Type}, {"HS2 Series - Copy.2", type text}}),
#"Removed Columns2" = Table.RemoveColumns(#"Changed Type3",{"Series Display Name - Copy.2", "HS2 Series - Copy.2"}),
#"Renamed Columns2" = Table.RenameColumns(#"Removed Columns2",{{"Series Display Name - Copy.1", "Series Display Code"}, {"HS2 Series - Copy.1", "Series Code"}})

T-SQL:

-- Assuming initial table is named 'YourTable' and has the necessary columns
WITH CTE AS (
SELECT DISTINCT
[Column1],
[Column2], -- and other columns
CASE
WHEN LEFT([Series Display Name], 1) = '.' THEN NULL
ELSE [Series Display Name]
END AS HS2
FROM YourTable
-- Add further steps here, like JOINs, WHERE clauses, and other transformations
-- ...
)
SELECT
[Column1],
[Column2], -- and other columns
HS2 AS [HS2 Series],
-- Further column transformations and calculations
-- ...
FROM CTE;