Page 2 of 2 FirstFirst 1 2
Results 11 to 12 of 12

Thread: Text.Combine if column contains text

  1. #11
    Conjurer Bill Szysz's Avatar
    Join Date
    Apr 2016
    Location
    Poznań, Poland Dublin, Ireland
    Posts
    103
    Articles
    0
    Excel Version
    Excel 365


    Register for a FREE account, and/
    or Log in to avoid these ads!

    I didn't notice that it is only subset of your table...sorry.
    So, as Cyborgski said, you should use Record.SelectFields and the list of fields. My proposition in the attachement.
    You can adjust the order of days of the week as you wish.
    Additionally, you can choose the language in which the column headers was written.
    Thanks Cyborgski for your kind words. :-) My name also ends in "ski"... Bill SZYSZkowski :-))
    By the way...this is only fun with the problem. I do not want to tell you which way is better one. As always it depends on many reasons.

    Full code:
    Code:
    let    Source = Excel.CurrentWorkbook(){[Name="Hours"]}[Content],
        List = 
            List.Buffer(
                List.Transform(
                    {0..6},
                     each Text.Start(
                        Text.Upper(
                            Date.DayOfWeekName(
                                Date.From(_),
                                "en-US"
                            )
                        ),
                        3
                    )
                )
            ),
    
    
        #"Added Custom" =
    	Table.AddColumn(
                Source,
    	    "Daily Notes",
    	    each Text.Combine(
    	 	   List.Transform(
    		      Table.ToRows(
            	         Table.SelectRows(
    			    Record.ToTable(
                                   Record.SelectFields(_, List)
                                ),
                                each [Value] is text
    			 )
    		      ),
    		      each "(" & Text.Combine(_, ") ")
    		   ),
    		   ", "
    		)
            )
    in
        #"Added Custom"
    Attached Files Attached Files

  2. #12
    Acolyte Nick Burns's Avatar
    Join Date
    May 2017
    Posts
    75
    Articles
    0
    Excel Version
    Office 365
    Some slick solutions here, though I think Mike/Cyborgski's solution is the shortest in terms of number of characters.
    I've since replaced my List.Accumulate() solution with his.

    Thanks again to everyone!
    Oh... by the way, YOU'RE WELCOME!

Page 2 of 2 FirstFirst 1 2

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •