Forum Discussion

abh1abh's avatar
abh1abh
Frequent Visitor
3 years ago
Solved

How to rewrite an sql code in power query M

Im not that good at power query, but im trying to learn it. 

 

I have some knowledge from working with databases. 

 

I want to rewrite this into power query's advanced editor:

(SELECT
Port_of_Loading,
nullif(min(iif( Shipping_Line = 'MSC', [20DC], 0 )),0) AS MSC,
nullif(min( iif( Shipping_Line = 'COSCO' , [20DC], 0 ) ),0) AS COSCO,
nullif(min( iif( Shipping_Line = 'YANGMING', [20DC], 0 ) ),0) AS YANGMING,
nullif(min( iif( Shipping_Line = 'HAPAG', [20DC], 0 ) ),0) AS HAPAG,
nullif(min( iif( Shipping_Line = 'CMA-NAK', [20DC], 0 ) ),0) AS [CMA-NAK],
nullif(min( iif( Shipping_Line = 'CMA-CGM', [20DC], 0 ) ),0) AS [CMA-CGM]
FROM
rate_oslo
GROUP BY
Port_of_Loading;

 

I have tried this (in bold, ignore the first part about the date and such):
let Source = Sql.Database("mantleanalytics.database.windows.net", "Hevold_Dev"), dbo_rate_oslo_test = Source{[Schema="dbo",Item="rate_oslo_test"]}[Data], #"Filtered Rows" = Table.SelectRows(dbo_rate_oslo_test, each [Start_Date] <= Date and [Stop_Date] >= Date), #"Selected Columns" = Table.SelectColumns(#"Filtered Rows", {"Port_of_Loading", "Shipping_Line", "20DC"}), #"Grouped Rows" = Table.GroupBy(#"Selected Columns", {"Port_of_Loading"}, {{"MSC", each List.Min(List.Select(Table.Column([Column1], "20DC"), each [Shipping_Line] = "MSC")), type number}, {"COSCO", each List.Min(List.Select(Table.Column([Column1], "20DC"), each [Shipping_Line] = "COSCO")), type number}, {"YANGMING", each List.Min(List.Select(Table.Column([Column1], "20DC"), each [Shipping_Line] = "YANGMING")), type number}, {"HAPAG", each List.Min(List.Select(Table.Column([Column1], "20DC"), each [Shipping_Line] = "HAPAG")), type number}, {"[CMA-NAK]", each List.Min(List.Select(Table.Column([Column1], "20DC"), each [Shipping_Line] = "CMA-NAK")), type number}, {"[CMA-CGM]", each List.Min(List.Select(Table.Column([Column1], "20DC"), each [Shipping_Line] = "CMA-CGM")), type number}}), #"Transformed Columns" = Table.TransformColumns(#"Grouped Rows", {{"MSC", each if [MSC]=0 then null else [MSC]}, {"COSCO", each if [COSCO]=0 then null else [COSCO]}, {"YANGMING", each if [YANGMING]=0 then null else [YANGMING]}, {"HAPAG", each if [HAPAG]=0 then null else [HAPAG]}, {"[CMA-NAK]", each if [CMA-NAK]=0 then null else [CMA-NAK]}, {"[CMA-CGM][CMA-CGM]", each if [CMA-CGM]=0 then null else [CMA-CGM]}})

in

#"Transformed Columns"

 

But I get an error that says:
Expression.Error: The column 'Column1' of the table wasn't found.
Details:
Column1

Any help would be great!

  • NewStep=Table.Pivot(#"Filtered Rows"[[Port_of_Loading],[Shipping_Line],[20DC]],{"MSC","COSCO","YANGMING“,”HAPAG","CMA-NAK","CMA-CGM"},"Shipping_Line","20DC",each List.Min(_)??0)

2 Replies

  • wdx223_Daniel's avatar
    wdx223_Daniel
    Community Champion

    NewStep=Table.Pivot(#"Filtered Rows"[[Port_of_Loading],[Shipping_Line],[20DC]],{"MSC","COSCO","YANGMING“,”HAPAG","CMA-NAK","CMA-CGM"},"Shipping_Line","20DC",each List.Min(_)??0)