none
Selection of Columns Dynamically in Power Query RRS feed

  • Question

  • I want to select all columns in power query dynamically.

    For instance 

    
    A B C
    1 1 1
    1 1 1
    1 1 1
    1 1 1

    If i add Column D in excel then my query steps should all work in Column D. 

    Wednesday, March 6, 2019 9:46 AM

Answers

  • Well, I'll try) I guess, the first line of code (i.e. 1-3 arguments of Table.SplitColumn function)

    split = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByDelimiter(";", 1),

    is familiar to you, since it's creating by default (I just replaced QuoteStyle.Csv for 1 for brevity).

    The 4th argument may be either list of columns {"Column1.1","Column1.2", ...} or number of columns, into which the initial column is splitting. Because this list/number of columns may vary, we need to define it for this specific dataset.

    So,

    1. We take initial column:

    Source[Column1]

    2. For each row, define, into how many columns initial value may be splitted:

    List.Count(Text.Split(_, ";"))

    3. For each row, transform initial value to number from previous step:

    List.Transform(Source[Column1], each List.Count(Text.Split(_, ";")))

    4. Calculate maximum of numbers from previous step

    List.Max(List.Transform(Source[Column1], each List.Count(Text.Split(_, ";"))))

    This code is already working, but column names are by default ("Column1.1", "Column1.2" etc.). To handle it, we assign the letters of alphabet to column names:

    List.FirstN({"A".."Z"}, List.Max(List.Transform(Source[Column1], each List.Count(Text.Split(_, ";")))))

    Hence, this code will not work correctly, if number of splitted columns is more than 26, but I hope, it's not the case)

    Thursday, March 7, 2019 8:50 AM

All replies

  • Hi Haider,

    It depends on your query, but in common case you may use this expression to refer to all your columns (including the new ones):

    = Table.ColumnNames(YourTable)
    Wednesday, March 6, 2019 11:48 AM
  • Hi Aleksei,

    When i split a column it creates multiple columns and those splitted columns do not merge dynamically.. 

    is there any way to handle this?

    Wednesday, March 6, 2019 11:58 AM
  • Well, in this case you may apply following approach:

    split = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByDelimiter(";", 1),
                List.FirstN({"A".."Z"}, List.Max(List.Transform(Source[Column1], each List.Count(Text.Split(_, ";"))))))
    

    Wednesday, March 6, 2019 2:55 PM
  • thanks for sharing.. but i m struggling to read in M Code. May you please write steps in simple words?..
    Wednesday, March 6, 2019 3:37 PM
  • Well, I'll try) I guess, the first line of code (i.e. 1-3 arguments of Table.SplitColumn function)

    split = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByDelimiter(";", 1),

    is familiar to you, since it's creating by default (I just replaced QuoteStyle.Csv for 1 for brevity).

    The 4th argument may be either list of columns {"Column1.1","Column1.2", ...} or number of columns, into which the initial column is splitting. Because this list/number of columns may vary, we need to define it for this specific dataset.

    So,

    1. We take initial column:

    Source[Column1]

    2. For each row, define, into how many columns initial value may be splitted:

    List.Count(Text.Split(_, ";"))

    3. For each row, transform initial value to number from previous step:

    List.Transform(Source[Column1], each List.Count(Text.Split(_, ";")))

    4. Calculate maximum of numbers from previous step

    List.Max(List.Transform(Source[Column1], each List.Count(Text.Split(_, ";"))))

    This code is already working, but column names are by default ("Column1.1", "Column1.2" etc.). To handle it, we assign the letters of alphabet to column names:

    List.FirstN({"A".."Z"}, List.Max(List.Transform(Source[Column1], each List.Count(Text.Split(_, ";")))))

    Hence, this code will not work correctly, if number of splitted columns is more than 26, but I hope, it's not the case)

    Thursday, March 7, 2019 8:50 AM
  • Hi Aleksei,

    Many thanks for trying to help. 

    This code will work only when all splitted columns name were changed to A..Z and it will be limited to 26 splitted columns. M i getting right?

    Thursday, March 7, 2019 4:53 PM
  • Hi Haider,

    Yes, you are getting right. But column names as letters was chosen because of your example. Of cource, you may use any list of preferable column names, for instance, numbers from 1 to 1000:

    = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByDelimiter(";", 1),
        List.FirstN(List.Transform({1..1000},Text.From), List.Max(List.Transform(Source[Column1], each List.Count(Text.Split(_, ";"))))))


    Friday, March 8, 2019 9:01 AM
  • Many thanks Aleksei for helping me. 

    May you please share your workbook with me with this example. It will help me alot. 

    haider.ali7333@gmail.com 


    Friday, March 8, 2019 9:08 AM
  • Sure, email was sent.
    Friday, March 8, 2019 9:32 AM
  • Hi Aleksei,

    I have checked your sheet and its that case which i was looking for. 

    I will try to explain you exact challenge to make it a solution of it. 

    For example

    asasas123ssadsjj7667xss9887xz878xsxxxzzxxz88878xz

    above data is from source and its without delimiter. 

    First i have to split them and extract only numbers with delimiters.

    Once we got our numbers we need to find maximum number in each row.

    in above case, 88878 is maximum number.

    Hard part is it should work even if source characters increases...

    I hope now you will get what i m actually looking for and help me to find a solution of it.

    Thanks 

    Haider Alee

      

    Sunday, March 10, 2019 9:41 AM
  • Hi Haider,

    It's look like task, completely different from start topic. As far as I understand, the result you need is maximum number from text value, not splitted columns. I guess, you should create new thread for this problem. If it's possible, with samples of initial dataset and the result you need.

    Monday, March 11, 2019 8:48 AM
  • okay i created another thread with complete detail of what result i want to get.
    Monday, March 11, 2019 4:56 PM