CODEWITHSUNDEEP
excelpowerquery
Kamosan
Kamosan

Reputation: 3

Excel Power Query Split Column by Number of Characters into rows but not duplicate values in a another column

I have a column of data (Request) that I have to split by number of characters 499 into rows. I have another column that has the number of items for that row (Items completed) that I need every subsequent row created from the split to display null or 0 instead of duplicating the value.

Table 1 is original data Table 2 is table after the split Table 3 is intended output with 0 or null on the split row

Removing duplicates removes the entire row but need the qualify removing the duplicates in the column based on the Ref ID since i need to be able to count the items completed without over inflating the numbers in the grand total

Tried removing duplicates but it removes the entire row.

enter image description here

Upvotes: 0

Views: 122

Answers (1)

horseyride
horseyride

Reputation: 21318

One way in powerquery

let  Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Grouped Rows" = Table.Group(Source, {"Name"}, {{"data", each 
    let a=   Table.ExpandListColumn(Table.TransformColumns(_, {{"Request", Splitter.SplitTextByRepeatedLengths(499)}}), "Request"),
    b = Table.RemoveColumns(a,{"Items Completed"})
    in  Table.FromColumns(Table.ToColumns(b) & {{a[Items Completed]{0}}}  ,Table.ColumnNames(b)&{"Items Completed"}), type table }}),
ColumnsToExpand = List.Difference(List.Distinct(List.Combine(List.Transform(Table.Column( #"Grouped Rows", "data"), each if _ is table then Table.ColumnNames(_) else {}))),{"Name"}),
#"Expanded data" = Table.ExpandTableColumn(#"Grouped Rows", "data", ColumnsToExpand,ColumnsToExpand)
in #"Expanded data"

enter image description here

Sample to tack onto existing code found in home...advanced editor...

existing code from home...advanced editor...:

 #"Something" = blah blah,
 #"SomethingSomething" = blah blah
 in #"SomethingSomething"

appended code from home...advanced editor...

 #"Something" = blah blah,
 #"SomethingSomething" = blah blah,
#"Grouped Rows" = Table.Group(#"SomethingSomething" , {"Name"}, {{"data", each 
    let a=   Table.ExpandListColumn(Table.TransformColumns(_, {{"Request", Splitter.SplitTextByRepeatedLengths(499)}}), "Request"),
    b = Table.RemoveColumns(a,{"Items Completed"})
    in  Table.FromColumns(Table.ToColumns(b) & {{a[Items Completed]{0}}}  ,Table.ColumnNames(b)&{"Items Completed"}), type table }}),
ColumnsToExpand = List.Difference(List.Distinct(List.Combine(List.Transform(Table.Column( #"Grouped Rows", "data"), each if _ is table then Table.ColumnNames(_) else {}))),{"Name"}),
#"Expanded data" = Table.ExpandTableColumn(#"Grouped Rows", "data", ColumnsToExpand,ColumnsToExpand)
in #"Expanded data"

Upvotes: 0

Related Questions