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://community.fabric.microsoft.com/t5/Desktop/Accessing-Binary-Columns-from-Oracle-Database/m-p/3666931].
I've successfully accessed binary data that was in my table, and now the data is formatted as follows:
| SERIAL_NUM | TEST_DATE | TEST_TIME | DATA_POINTS | All |
| 55559 | 08-19-2021 | 10:42:27 | 1087 | [Table] |
| 55573 | 08-19-2021 | 10:42:27 | 1087 | [Table] |
| 55569 | 08-19-2021 | 10:42:27 | 1087 | [Table] |
| 55563 | 08-19-2021 | 10:42:27 | 1087 | [Table] |
| 55520 | 08-19-2021 | 10:42:27 | 1087 | [Table] |
| 55562 | 08-19-2021 | 10:42:27 | 1087 | [Table] |
| 55464 | 08-19-2021 | 10:42:27 | 1087 | [Table] |
| 55505 | 08-19-2021 | 10:42:27 | 1087 | [Table] |
| 55585 | 08-19-2021 | 10:42:27 | 1087 | [Table] |
| 55554 | 08-19-2021 | 10:42:27 | 1087 | [Table] |
In the "ALL" column, each table contains data with around 1000 columns. For example, the table associated with serial number 55559 looks like this:
| Data 1 | Data 2 | Seconds |
| 752 | 784 | 0 |
| 263 | 4 | 1 |
| 343 | 462 | 2 |
| 498 | 512 | 3 |
| 367 | 93 | 4 |
| 645 | 137 | 5 |
| 154 | 980 | 6 |
| …. | …. | …. |
| 906 | 83 | 1078 |
| 433 | 260 | 1079 |
| 338 | 911 | 1080 |
| 582 | 401 | 1081 |
| 367 | 204 | 1082 |
| 114 | 383 | 1083 |
| 853 | 349 | 1084 |
| 657 | 350 | 1085 |
| 738 | 24 | 1086 |
| 890 | 667 | 1087 |
And this is what I have in Power Query when I filter by one serial number to see the data (DATA_POINTS represents how many rows inside every single table) :
With around 3 million unique serial numbers and each serial number expanding to around 1000 rows, the resulting dataset becomes substantial. I've attempted to expand the table using Power Query, but the process is taking a long time.
My primary goal is to optimize this expanding process, as I need the data within the "Data 1" and "Data 2" columns in the "ALL" column. This data will be used for plotting in a line chart and performing some simple calculations.
However, when attempting to expand the table, it remains stuck at this message for over 3 hours, with no clue of the progress or remaining rows:
I would appreciate any insights you may have regarding this issue. Thank you.
8 Replies
- lbendlinSuper User
How much memory does your PC have? Try using Binary.Buffer or Table.Buffer .
- AnonymousNot 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"- lbendlinSuper User
use a Binary.Buffer on the source.
- AnonymousNot applicable
lbendlinAt what step please? It won't work on the source, gives an Expression.Error: We cannot convert a value of type Table to type Binary.
- lbendlinSuper User
Then use Table.Buffer instead.
- AnonymousNot applicable
lbendlin It didn't change the speed much, it is still so slow