cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sofia
New Member

Split by delimiter and autofill to next row (without duplicating)

Hi,

 

I have an issue where my source data looks like this, i.e data is split into rows, but the references are not. 

sofia_1-1668173078774.png

I would want the reference numbers to be split by delimiter and moved into the next row. Expected result:

Trans. Ref.Book Ref.Index
205461349120
205471349221

 

I I split by delimiter I get duplicate values and I can't figure out how to get rid of them to keep only the relevant rows.

Any help would be much appreciated.

1 ACCEPTED SOLUTION

Hi! you want something like this?

 

 

 

let
    Source = #table(
        {"Trans. Ref", "Book. Ref", "Index"}, 
        {   {"20546, 20547", "13491, 13492", 20},
            {"20546, 20547", "13491, 13492", 21},
            {"20548, 20549, 20550", "13493, 13494, 13495", 22},
            {"20548, 20549, 20550", "13493, 13494, 13495", 23},
            {"20548, 20549, 20550", "13493, 13494, 13495", 24}
        }),
    Headers = List.Buffer( Table.ColumnNames(Source) ),
    Grouped = Table.Group(Source, {"Trans. Ref", "Book. Ref"}, {{"l", each Table.ToColumns( Table.RemoveColumns(_,{"Trans. Ref", "Book. Ref"}) ), type list}}),
    Split = Table.TransformColumns(Grouped,{{"Trans. Ref", Splitter.SplitTextByDelimiter(", "), type text}, {"Book. Ref", Splitter.SplitTextByDelimiter(", "), type text}}),
    MergedColumns = Table.CombineColumns( Split,{"Trans. Ref", "Book. Ref","l"}, (x)=>Table.FromColumns( {x{0}} & {x{1}} & x{2}, Headers ), "Out" ),
    Expanded = Table.ExpandTableColumn( MergedColumns, "Out", Headers )
in
    Expanded

 

 

 

 

View solution in original post

7 REPLIES 7
sofia
New Member

Thanks but not exactly. I basically just want unique values in book ref and trans ref. by transferring the values to the nex row. The source date can look like this

Pay. DateOrder Ref.Trans. Ref.Book. RefPay. Ref.Payments Value TotalIndex
2022-08-06 14:33:32392920530137044050160011
2022-08-06 14:29:21392620525, 20526, 20527, 2052813700, 13701, 13702, 137034046160013
2022-08-06 14:29:22392620525, 20526, 20527, 2052813700, 13701, 13702, 13703404780014
2022-08-06 14:29:22392620525, 20526, 20527, 2052813700, 13701, 13702, 13703404880015
2022-08-06 14:29:22392620525, 20526, 20527, 2052813700, 13701, 13702, 13703404980016

And I would want it to look like this

Pay. DateOrder Ref.Trans. Ref.Book. RefPay. Ref.Payments Value TotalIndex
2022-08-06 14:33:32392920530137044050160011
2022-08-06 14:29:21392620525137004046160013
2022-08-06 14:29:2239262052613701404780014
2022-08-06 14:29:2239262052713702404880015
2022-08-06 14:29:2239262052813703404980016

 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("tc1LCsMwDIThqxitHZBGsvy4SvD9r1HHKqQUumtWH7OZ/zwJDBzcDvYkNlSHgjJpR1+Ai/JStLItjcuezhuhmb8f0AckHjweUHK68KAG7X3LOV1IgEB3y/yjpT9a+FOrLlqk7OFUu1Pl4VS/U05zvgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Pay. Date" = _t, #"Order Ref." = _t, #"Trans. Ref." = _t, #"Book. Ref" = _t, #"Pay. Ref." = _t, #"Payments Value Total" = _t, Index = _t]),
    TR_BR = List.Buffer( {"Trans. Ref.", "Book. Ref"} ),
    Headers = List.Buffer( TR_BR & List.Select( Table.ColumnNames(Source), each not List.Contains(TR_BR, _) ) ),
    Grouped = Table.Group(Source, TR_BR, {{"l", each Table.ToColumns( Table.RemoveColumns(_,TR_BR) ), type table}}),
    Split = Table.TransformColumns(Grouped,{{"Trans. Ref.", Splitter.SplitTextByDelimiter(", "), type list}, {"Book. Ref", Splitter.SplitTextByDelimiter(", "), type list}}),
    MergedColumns = Table.CombineColumns( Split, TR_BR & {"l"}, (x)=>Table.FromColumns( {x{0}} & {x{1}} & x{2}, Headers ), "Out" ),
    Expanded = Table.ExpandTableColumn( MergedColumns, "Out", Headers )
in
    Expanded

 

 

ToddChitt
Solution Sage
Solution Sage

But 20546 is 'linked' to Index of 20, AND to Index of 21. So is 20547. And so are both 13491 and 13492.

In your new data, you will lose that.

 

How do you determine that only the FIRST instance (top row) of 20546 gets associated with Index of 20?

The index represent the row number from the source data (I added it) and I can see that the source data has splitted the payments into rows that can be related to products in another table (by book ref). The logic seem to be that the 1st row relates to the first book ref, the 2nd to the second row etc. The Book refs have not been splitted between the rows though. 

 

sofia_0-1668185168752.png

 

Hi! you want something like this?

 

 

 

let
    Source = #table(
        {"Trans. Ref", "Book. Ref", "Index"}, 
        {   {"20546, 20547", "13491, 13492", 20},
            {"20546, 20547", "13491, 13492", 21},
            {"20548, 20549, 20550", "13493, 13494, 13495", 22},
            {"20548, 20549, 20550", "13493, 13494, 13495", 23},
            {"20548, 20549, 20550", "13493, 13494, 13495", 24}
        }),
    Headers = List.Buffer( Table.ColumnNames(Source) ),
    Grouped = Table.Group(Source, {"Trans. Ref", "Book. Ref"}, {{"l", each Table.ToColumns( Table.RemoveColumns(_,{"Trans. Ref", "Book. Ref"}) ), type list}}),
    Split = Table.TransformColumns(Grouped,{{"Trans. Ref", Splitter.SplitTextByDelimiter(", "), type text}, {"Book. Ref", Splitter.SplitTextByDelimiter(", "), type text}}),
    MergedColumns = Table.CombineColumns( Split,{"Trans. Ref", "Book. Ref","l"}, (x)=>Table.FromColumns( {x{0}} & {x{1}} & x{2}, Headers ), "Out" ),
    Expanded = Table.ExpandTableColumn( MergedColumns, "Out", Headers )
in
    Expanded

 

 

 

 

@Poohkrd This is really nice! Here's my variation of your code in case you're interested:

 

let
    Source = #table(
        {"Trans. Ref.", "Book. Ref", "Index"}, 
        {   {"20546, 20547", "13491, 13492", 20},
            {"20546, 20547", "13491, 13492", 21},
            {"20548, 20549, 20550", "13493, 13494, 13495", 22},
            {"20548, 20549, 20550", "13493, 13494, 13495", 23},
            {"20548, 20549, 20550", "13493, 13494, 13495", 24}
        }),
    Headers = List.Buffer(Table.ColumnNames(Source)),
    TrBr = {"Trans. Ref.", "Book. Ref"},
    OtherCols = List.RemoveItems(Headers, TrBr),
    Grouped = Table.Group(Source, TrBr, {{"OtherCols", each Table.RemoveColumns(_, TrBr), type table}}),
    TransformList = List.Transform(TrBr, each {_, Splitter.SplitTextByDelimiter(", "), type text}),
    Split = Table.TransformColumns(Grouped, TransformList),
    MergedColumns = Table.TransformRows(Split, each Table.FromColumns({[#"Trans. Ref."], [#"Book. Ref"]} & Table.ToColumns([OtherCols]), TrBr & OtherCols)),
    Combined = Table.Combine(MergedColumns),
    #"Reordered Columns" = Table.ReorderColumns(Combined, Headers)
in
    #"Reordered Columns"

 

 

@sofia Please give full credit to Pookrd for this solution. I came up with a different solution on my own but it isn't as robust or generalizable as this one but I'm including it as an alternative approach that might be easier to follow:

 

let
    Source = #table(
        {"Trans. Ref", "Book. Ref", "Index"}, 
        {
            {"20546, 20547", "13491, 13492", 20},
            {"20546, 20547", "13491, 13492", 21},
            {"20548, 20549, 20550", "13493, 13494, 13495", 22},
            {"20548, 20549, 20550", "13493, 13494, 13495", 23},
            {"20548, 20549, 20550", "13493, 13494, 13495", 24}
        }),
    Headers = List.Buffer(Table.ColumnNames(Source)),
    NonIndexCols = List.RemoveItems(Headers, {"Index"}),
    Grouped = Table.Group(Source, NonIndexCols, {}),
    TransRef = List.Union(List.Transform(Grouped[Trans. Ref], each Text.Split(_, ", "))),
    BookRef = List.Union(List.Transform(Grouped[Book. Ref], each Text.Split(_, ", "))),
    ConstructTable = Table.FromColumns({TransRef, BookRef, Source[Index]}, Headers)
in
    ConstructTable

 

@AlexisOlson Thanks for the compliment. I have already provided the correct code for the new source from @sofia. I didn't take into consideration the order of column names there.

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors