Forum Discussion
Table Expansion Optimization in Power Query
Hello, I am reaching out to discuss a challenge I've encountered while working with binary data from an Oracle Database, following up on a question I posted last week [link to the post: https://comm...
How much memory does your PC have? Try using Binary.Buffer or Table.Buffer .
Hi lbendlin , here is my device specs:
I am using Table.Buffer and List.Buffer, but it's downloading a billion row in a 1 row increment. I am using the following code now:
let
Source = ...,
GSDATA = ...,
....= ...,
#"Removed Other Columns" = Table.SelectColumns(ECHEM_DTL,{"SERIAL_NUM", "TEST_DATE", "TEST_TIME", "CFG_FILE", "DATA_ARRAY1", "DATA_ARRAY2", "DATA_POINTS"}),
#"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns",{{"TEST_DATE", type date}, {"TEST_TIME", type time}}),
#"Added Custom3" = Table.AddColumn(
#"Removed Other Columns",
"ALL",
each Table.Buffer(Table.AddIndexColumn(
Table.Combine(
List.Transform(
List.Zip(
{
List.Buffer(List.FirstN(
List.Transform(
Binary.Split([DATA_ARRAY1], 4),
each BinaryFormat.ByteOrder(
BinaryFormat.Record([val = BinaryFormat.SignedInteger32]),
ByteOrder.LittleEndian
)(_)[val]
),
[DATA_POINTS]
)),
List.Buffer(List.FirstN(
List.Transform(
Binary.Split([DATA_ARRAY2], 4),
each BinaryFormat.ByteOrder(
BinaryFormat.Record([val = BinaryFormat.SignedInteger32]),
ByteOrder.LittleEndian
)(_)[val]
),
[DATA_POINTS]
))
}
),
(x) => Table.FromRows({x}, {"DATA1", "DATA2"})
)
),
"Secondes",
1,
1
)
)),
#"Expanded ALL" = Table.ExpandTableColumn(
#"Added Custom3",
"ALL",
{"DATA1", "DATA2", "Secondes"},
{"DATA1", "DATA2", "Secondes"}
),
#"Removed Columns" = Table.RemoveColumns(#"Expanded ALL",{"CFG_FILE", "DATA_ARRAY1", "DATA_ARRAY2","DATA_POINTS"})//Here I am removing the binary columns
in
#"Removed Columns"use a Binary.Buffer on the source.
lbendlin Still very slow, do you think it would possible to achieve the results by passing the serial number as a parameter from the report to power query? I tried doing this but the direct query won't allow me to do the transformation with the binary data. But I think expanding the tables for the serial numbers the users choose could eliminate the need to deal with the billions rows.
please provide a meaningful amount of sample data that illustrates the issue.