none
Table.SplitColumn RRS feed

  • Question

  • Hi: I was wondering if there is a way "Table.SplitColumn" can be work dynamically. Following is a scenario where my source data has 8 levels and hence it has been split into 8 columns. Next time if my source data only has 5 levels, the following code wont work because it has been hard coded by PQ. How do I make it dynamic?

     SplitColumnbyDelimiter = Table.SplitColumn(#"Added Custom2", "Level", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Level.1", "Level.2", "Level.3", "Level.4", "Level.5", "Level.6", "Level.7", "Level.8"})

    Similarly for Table.ReplaceValue function, can I make it work dynamically?

     #"Replaced Value1" = Table.ReplaceValue(#"Split Column by Delimiter","",null,Replacer.ReplaceValue,{"Level.1", "Level.2", "Level.3", "Level.4", "Level.5", "Level.6", "Level.7", "Level.8"})

    Any tips would be greatly appreciated.

    Friday, May 17, 2019 11:14 AM

Answers

  • Hi,

    As an option, you may create following step (in any part of the query - after Source step, for example):

    maxPos = List.Max(Positions[Index])

    And then refer to it in the formula:

    #"Added Custom2" = Table.AddColumn(#"Replaced Value1", "Custom",
                       each Text.Combine(List.Repeat({[Text After Delimiter]},maxPos-[Position]), "|"))

    You may also refer to it in this step (if logic for this number is same):

    #"Split Column by Delimiter" = Table.SplitColumn(#"Added Custom3", "Level",Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), maxPos-1),

    • Marked as answer by M.Awal Thursday, May 30, 2019 7:08 AM
    Tuesday, May 28, 2019 3:48 PM

All replies

  • Hi: Thanks for your reply. I tried to follow as per the above but it doesn't work in my example. As per the link below i have highlighted the code in yellow, where I would like to make it dynamic. Imke originally provided this code (great work).

    CC

    Monday, May 20, 2019 10:51 AM
  • Hi,

    Well, you may replace highlited code for this one:

        #"Split Column by Delimiter" = Table.SplitColumn(#"Added Custom5", "Level", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), List.Max(List.Transform(Source[Column1], each List.Count(Text.Split(_, "|"))))),
        #"Replaced Value2" = Table.ReplaceValue(#"Split Column by Delimiter","",null,Replacer.ReplaceValue, List.Select(Table.ColumnNames(#"Split Column by Delimiter"), each Text.StartsWith(_,"Level"))),
        #"Filled Down" = Table.FillDown(#"Replaced Value2", List.Select(Table.ColumnNames(#"Replaced Value2"), each Text.StartsWith(_,"Level"))),
    Monday, May 20, 2019 11:23 AM
  • Hi :

    Thanks for your help. However, its not showing any level after level-9. Please see the link below.

    CC

    Monday, May 27, 2019 10:35 AM
  • Hi,

    Well, I get it. I didn't deep dive into your code and guessed, that number of levels from initial column wasn't modified. So, you may just replace this line:

    #"Split Column by Delimiter" = Table.SplitColumn(#"Added Custom3", "Level", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), List.Max(List.Transform(#"Added Custom3"[Level], each List.Count(Text.Split(_, "|"))))),
    Monday, May 27, 2019 11:44 AM
  • Hi :

    Thanks it works now. All I had to do is subtract 1 where List.Max is.

    One more question in relation to the link above, to replace the hardcoded 14 below, I used Table.RowCount(Positions) (step#11). As soon as I use Table.RowCount it gets very slow. Do you know better work around?

    #"Added Custom3" = Table.AddColumn(#"Replaced Value2", "Custom", each Text.Combine(List.Repeat({[Text After Delimiter]},14-[Position]), "|")),

    Tuesday, May 28, 2019 5:58 AM
  • Hi,

    As an option, you may create following step (in any part of the query - after Source step, for example):

    maxPos = List.Max(Positions[Index])

    And then refer to it in the formula:

    #"Added Custom2" = Table.AddColumn(#"Replaced Value1", "Custom",
                       each Text.Combine(List.Repeat({[Text After Delimiter]},maxPos-[Position]), "|"))

    You may also refer to it in this step (if logic for this number is same):

    #"Split Column by Delimiter" = Table.SplitColumn(#"Added Custom3", "Level",Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), maxPos-1),

    • Marked as answer by M.Awal Thursday, May 30, 2019 7:08 AM
    Tuesday, May 28, 2019 3:48 PM