I need help.
I have a large unpivoted dataset and need to pivot it.
This works in Excel, (about 5 minutes), but when I use this function in PQ Online, the Pivot operations (which is a full scan operations) causes the query to time out (more than 10 minutes, even for the preview). So, apparently complex group bys and pivots just aren't possible with Dataflows yet.
Does anyone know if there may be a way to do this using something fast like Scala, in Data Pipelines?
Function:
(InputTable as table) =>
let
#"Pivoted Column" = Table.Pivot(
InputTable, List.Distinct(
InputTable[Line_Item]
),
"Line_Item",
"Amount",
List.Sum
),
#"Replaced Nulls with Zeros" = Table.ReplaceValue(
#"Pivoted Column",
null,
0,
Replacer.ReplaceValue,
{
"Revenue",
"Cost",
"Capex"
}
),
AddProfit = Table.AddColumn(
#"Replaced Nulls with Zeros",
"Profit",
each try
[#"Revenue"] +
[#"Cost"] +
catch (e) => e
),
AddPreTax = Table.AddColumn(
AddProfit,
"PreTax",
each try
[#"Profit"] +
[#"Capex"]
catch (e) => e
),
OutputTable = Table.UnpivotOtherColumns(
AddPreTax, {
"Project Name",
"Date",
},
"Line_Item",
"Amount"
)
in
OutputTable
