Forum Discussion
Unpivot (Append?) Repeating column formats
Hi pistachio
sorry, just read your second comment previously. you can try the following technique, less "intelligent" action required:
let Source = Web.Page(Web.Contents("https://community.powerbi.com/t5/Desktop/Unpivot-Append-Repeating-column-formats/m-p/964070/highlight/false#M462025")), Data0 = Source{0}[Data], #"Promoted Headers" = Table.PromoteHeaders(Data0, [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"ID", Int64.Type}, {"Datetime", type datetime}, {"Value", Int64.Type}, {"", type text}, {"ID_1", Int64.Type}, {"Datetime_2", type datetime}, {"Value_3", type number}, {"_4", type text}, {"ID_5", Int64.Type}, {"Datetime_6", type datetime}, {"Value_7", type number}, {"_8", type text}, {"ID_9", Int64.Type}, {"Datetime_10", type datetime}, {"Value_11", type number}}), Custom1 = Table.ToColumns(#"Changed Type"), #"Converted to Table" = Table.FromList(Custom1, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Added Index" = Table.AddIndexColumn(#"Converted to Table", "Index", 0, 1), #"Integer-Divided Column" = Table.TransformColumns(#"Added Index", {{"Index", each Number.IntegerDivide(_, 4), Int64.Type}}), #"Grouped Rows" = Table.Group(#"Integer-Divided Column", {"Index"}, {{"Partition", each Table.FromColumns(_[Column1]), type table [Column1=list, Index=number]}}, GroupKind.Local), Custom2 = Table.Combine(#"Grouped Rows"[Partition]) in Custom2For performance it is crucial to use the "GroupKind.Local" in step "Grouped Rows"
Please let me know about the performance difference to the first Pivot-solution, thanks.
Please not that for this solution it is crucial that you always have the same number of columns per repetition!!
So, there are probably easier ways to do this and ImkeF probably has the solution. But, worst case, create a query from the Excel file and remove all but the first 3 columns. Create a second query, choose the next 3 columns (ID_1, Datetime_1, Value_1) and remove all the other columns. Rename the columns to ID, Datetime, Value. Rinse and repeat the same steps for the second query for a third, fourth, fifth query, etc. Then, use an Append query to append them all together.
Yep cheers I've done this but each query seems like it has to reload the 55mb workbook so it takes a long time to get all together. Wondering if there was a better way
Hi pistachio
sorry, just read your second comment previously. you can try the following technique, less "intelligent" action required:
let Source = Web.Page(Web.Contents("https://community.powerbi.com/t5/Desktop/Unpivot-Append-Repeating-column-formats/m-p/964070/highlight/false#M462025")), Data0 = Source{0}[Data], #"Promoted Headers" = Table.PromoteHeaders(Data0, [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"ID", Int64.Type}, {"Datetime", type datetime}, {"Value", Int64.Type}, {"", type text}, {"ID_1", Int64.Type}, {"Datetime_2", type datetime}, {"Value_3", type number}, {"_4", type text}, {"ID_5", Int64.Type}, {"Datetime_6", type datetime}, {"Value_7", type number}, {"_8", type text}, {"ID_9", Int64.Type}, {"Datetime_10", type datetime}, {"Value_11", type number}}), Custom1 = Table.ToColumns(#"Changed Type"), #"Converted to Table" = Table.FromList(Custom1, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Added Index" = Table.AddIndexColumn(#"Converted to Table", "Index", 0, 1), #"Integer-Divided Column" = Table.TransformColumns(#"Added Index", {{"Index", each Number.IntegerDivide(_, 4), Int64.Type}}), #"Grouped Rows" = Table.Group(#"Integer-Divided Column", {"Index"}, {{"Partition", each Table.FromColumns(_[Column1]), type table [Column1=list, Index=number]}}, GroupKind.Local), Custom2 = Table.Combine(#"Grouped Rows"[Partition]) in Custom2For performance it is crucial to use the "GroupKind.Local" in step "Grouped Rows"
Please let me know about the performance difference to the first Pivot-solution, thanks.
Please not that for this solution it is crucial that you always have the same number of columns per repetition!!
This is really great solution!
That works great! I struggle to understand the steps but was able to replicate it across all of my data thank you.
The performance was about twice as fast as the original method (make individual query for each row grouping, append into one query). Interestingly, the size of data pulled in is still much larger than the Excel sheet i.e. the excel sheet is 55mb, but when refreshing the .pbix file the progress bar loads upwards of 200mb from that query.
Still, very pleased thank you