Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to Solution.
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)
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)
Thank you so much!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.