Forum Discussion

Sachintha's avatar
Sachintha
Icon for Helper III rankHelper III
2 years ago
Solved

Split by delimeter AND pivot at the same time?

I have a data file that looks like this:

Unit,WW,Counts
U1,38,"A(1007),B(15),C(4408),E(800),F(411),G(16)"
U2,38,"B(254),C(36015),D(55),E(7555),F(4159),G(109)"
U3,38,"A(1026),B(7),D(4),E(545),G(12)"
U4,38,"A(10473),B(254),C(37905),D(29),E(5527),F(2429),G(284)"
U5,38,"B(9),C(4037),D(5),E(631),F(343),G(12)"
U6,38,"A(1051),B(37),E(321),F(193),G(2)"
U7,38,"A(1062),B(38),E(399),F(218),G(3)"

 

I get this same file in this same format from the customer each week, which is represented by the WW (Work Week) column. The Unit column, you can think of it as an ID. I'll get this data for the same set of units each week, and each will will only have one U1 row, one U2 row, etc.

 

The 3rd 'Counts' column is the most important one. It has a set of counts by category. So, for WW38, the U1 unit has a count of 1007 for category A, and 15 for category B. Furthermore, I can't know in advance how many categories there will be in each row, or what they are, as obvious from the data set. Furthermore, there's the possibility of new categories being added in the future without warning. So, it's possible to have, for instance, a value of K(223) in WW40 file for U1. For reasons I can't go into, I have no control over the format of this file.

 

My requirement is to accumulate these files week by week and pull all the data into a single PBI file. While doing so, I'd like to split the Counts column into multiple columns, and have the categories as column titles while the numbers in the parenthesis be cell values.

 

So, if we imagine we only had the top two rows in the file (for convenience), I'd like my data imported to PBI to look like this:

ID,WW,A, B, C, D, E, F, G
U1,38,1007,15,4408,null,800,411,16
U2,38,null,254,36016,55,7555,4159,109

 

I can *sort of* get this result if I split by commas, then further split each column by "(" etc, but this is going to break it if there was a new category. Is there a better way of doing this, or am I really out of options becuase of the terrible file format?

 

Need Help
Show and Tell
Tips and Tricks
  • AlienSx's avatar
    AlienSx
    2 years ago

    Sachintha this code transforms your original table into the table with categories as columns providing that you have a text w/o double quotes in "count" columns. Otherwise remove double quotes in the code or add them to delimiters list and correct List.Alternate parameters if needed.

    let
    Source = your_table,
    rs = List.Buffer(Table.ToRecords(Source)),
    fx_r = (r as record) as table => 
        [count = Splitter.SplitTextByAnyDelimiter({"(", "),", ")"})(r[Count]),
        cat = List.RemoveLastN(List.Alternate(count, 1, 1, 1), 1),
        values = List.Alternate(count, 1, 1, 0),
        out = 
            #table(
                {"Unit", "WW"} & cat,
                {{r[Unit], r[WW]} & values})][out],
    z = Table.Combine(List.Transform(rs, fx_r))
in
    z

     

1 Reply

  • AlienSx's avatar
    AlienSx
    Icon for Super User rankSuper User
    2 years ago

    Sachintha this code transforms your original table into the table with categories as columns providing that you have a text w/o double quotes in "count" columns. Otherwise remove double quotes in the code or add them to delimiters list and correct List.Alternate parameters if needed.

    let
    Source = your_table,
    rs = List.Buffer(Table.ToRecords(Source)),
    fx_r = (r as record) as table => 
        [count = Splitter.SplitTextByAnyDelimiter({"(", "),", ")"})(r[Count]),
        cat = List.RemoveLastN(List.Alternate(count, 1, 1, 1), 1),
        values = List.Alternate(count, 1, 1, 0),
        out = 
            #table(
                {"Unit", "WW"} & cat,
                {{r[Unit], r[WW]} & values})][out],
    z = Table.Combine(List.Transform(rs, fx_r))
in
    z