May 21 2020 07:09 AM
Hi,
I have a customer that likes the output but would like common rows to be consolidated into one row. Example: 3 rows have the same "Loop Number" but columns "IO1-IO8 Loop Number" may have separate data.
Is it possible to do or do I need to read into an array to write out a new table?
May 21 2020 07:46 AM
SolutionYou may reference on the query and
1) Merge all NNN Loop Number columns into one column, let say Merged
2) Group By Loop Number with aggregating Merged column on Sum
3) Change in formula bar List.Sum([Merged]) on Text.Combine([Merged],",")
Reference on initial query once more and merge with previous one
May 21 2020 09:26 AM
I followed up to step 3 but some of the rows have more delimiters that the 8 columns merged. When I split the merged column I end up with 24 columns instead of the 8 original columns.
May 21 2020 09:44 AM
If you mean that delimiter sign is within texts, you may use any other one, e.g. "<SEP>" both on merging the columns and Text.Combine().
May 21 2020 10:35 AM
No issue with delimiter.. what's happening is if a common row has 3 loop number columns filled, then that is 8x3 = 24 columns are created (appended) without order, and not merged for the original 8 columns :
May 21 2020 11:16 AM
So, for such sample
it shall be k2,k1 not k1,k2 ?
Why do you have commas for the null values?
May 21 2020 11:16 AM
Many Thanks, after tweaking the following worked (Group By Advanced):
= Table.Group(#"Sorted Rows", {"Loop Number"}, {{"Sum1", each Text.Combine([IO1 Loop Number],","), type text}, {"Sum2", each Text.Combine([IO2 Loop Number],","), type text}, {"Sum3", each Text.Combine([IO3 Loop Number],","), type text}, {"Sum4", each Text.Combine([IO4 Loop Number],","), type none}, {"Sum5", each Text.Combine([IO5 Loop Number],","), type none}, {"Sum6", each Text.Combine([IO6 Loop Number],","), type text}, {"Sum7", each Text.Combine([IO7 Loop Number],","), type none}, {"Sum8", each Text.Combine([IO8 Loop Number],","), type none}})
May 21 2020 11:29 AM
@recho , you are welcome, glad to know you sorted this out
May 21 2020 07:46 AM
SolutionYou may reference on the query and
1) Merge all NNN Loop Number columns into one column, let say Merged
2) Group By Loop Number with aggregating Merged column on Sum
3) Change in formula bar List.Sum([Merged]) on Text.Combine([Merged],",")
Reference on initial query once more and merge with previous one