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.
I have this table, make like this:
let
Source = Sql.Database("mantleanalytics.database.windows.net", "Hevold_Dev"),
dbo_rate_oslo_all = Source{[Schema="dbo",Item="rate_oslo_all"]}[Data],
#"Filtered Rows" = Table.SelectRows(dbo_rate_oslo_all, each [Start_Date] <= Date_oslo and [Stop_Date] >= Date_oslo),
#"Pivoted Column" = Table.Pivot(#"Filtered Rows"[[Port_of_Loading],[Shipping_Line],[40DC]],{"MSC","COSCO","YANGMING","HAPAG","CMA-NAK","CMA-CGM"},"Shipping_Line","40DC",each List.Min(_))
in
#"Pivoted Column"
However, I now need to pivot based on latest date and grouped by "Shipping_Line", "Port_of_Loading", "Port_of_Discharge".
I have tried to do this:
let
Source = Sql.Database("mantleanalytics.database.windows.net", "Hevold_Dev"),
dbo_rate_oslo_all = Source{[Schema="dbo",Item="rate_oslo_all"]}[Data],
#"Filtered Rows" = Table.SelectRows(dbo_rate_oslo_all, each [Start_Date] <= Date_oslo and [Stop_Date] >= Date_oslo),
#"Filtered Rows1" = Table.Group(
#"Filtered Rows",
{"Shipping_Line", "Port_of_Loading", "Port_of_Discharge","20DC"},
{
{"max_start_date", each List.Max([Start_Date]), type nullable date}
}
)
in
#"Filtered Rows"
But I cant get it to pivot based on the max date.
This how i tried it:
let
Source = Sql.Database("mantleanalytics.database.windows.net", "Hevold_Dev"),
dbo_rate_oslo_all = Source{[Schema="dbo",Item="rate_oslo_all"]}[Data],
#"Filtered Rows" = Table.SelectRows(dbo_rate_oslo_all, each [Start_Date] <= Date_oslo and [Stop_Date] >= Date_oslo),
#"Filtered Rows1" = Table.Group(
#"Filtered Rows",
{"Shipping_Line", "Port_of_Loading", "Port_of_Discharge","20DC"},
{
{"max_start_date", each List.Max([Start_Date]), type nullable date}
}
),
#"Pivoted Column" = Table.Pivot(
#"Filtered Rows1"[[Port_of_Loading],[Shipping_Line],[20DC]],
{"MSC","COSCO","YANGMING","HAPAG","CMA-NAK","CMA-CGM"},
"Shipping_Line",
"20DC",
each List.Min(_)
)
in
#"Pivoted Column"
If there is any help i would really appreciate it
Grouping your data is a good thing to do if you only need summarized data; however, pivoting is usually not a good practice for analysis. Leave it unpivoted for easiest analysis, and pivot it in a matrix (by putting the date column on columns) when needed.
Pat
I totally agree! However this is for presenting the data for a department. For analysis i usally work this other programs like python databases, but this department wants an excel file to acess the information
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.