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!
