Forum Discussion
Anonymous
2 years agoNot applicable
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...
lbendlin
2 years agoSuper User
How much memory does your PC have? Try using Binary.Buffer or Table.Buffer .
- Anonymous2 years agoNot applicable
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"- lbendlin2 years agoSuper User
use a Binary.Buffer on the source.
- Anonymous2 years agoNot applicable
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.