Page 1 of 2 1 2 LastLast
Results 1 to 10 of 12

Thread: Text.Combine if column contains text

  1. #1
    Acolyte Nick Burns's Avatar
    Join Date
    May 2017
    Posts
    75
    Articles
    0
    Excel Version
    Office 365

    Text.Combine if column contains text



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

    I have columns of hours, but the users may enter in notes for the days they are off.
    I'd like to be able to capture those "daily notes" into a new column, so only combine the columns that have text.

    Bonus if it can reference which column the notes are in, if not that's not as prudent.

    attached is a sample file and the expected results

    Click image for larger version. 

Name:	DailyNotes.PNG 
Views:	12 
Size:	12.5 KB 
ID:	9417

    DailyNotes.xlsx
    Oh... by the way, YOU'RE WELCOME!

  2. #2
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,681
    Articles
    0
    Excel Version
    O365
    Code:
    let
        Source = Excel.CurrentWorkbook(){[Name="Hours"]}[Content],
        data.Typed = Table.TransformColumnTypes(Source,{{"SAT", Int64.Type}, {"SUN", Int64.Type}, {"MON", Int64.Type}, {"TUE", type any}, {"WED", type any}, {"THU", type any}, {"FRI", Int64.Type}}),
        indexcol.Add = Table.AddIndexColumn(data.Typed, "Index", 0, 1),
        indexCol.Unpivot = Table.UnpivotOtherColumns(indexcol.Add, {"Index"}, "Attribute", "Value"),
        notesCol.Add = Table.AddColumn(indexCol.Unpivot, "Daily Notes", each if Text.StartsWith([Value], "OFF") then "(" & [Attribute] & ") " & [Value] else null),
        errors.Replace = Table.ReplaceErrorValues(notesCol.Add, {{"Daily Notes", null}}),
        oldcols.Remove = Table.RemoveColumns(errors.Replace,{"Attribute", "Value"}),
        index.GroupBy = Table.Group(oldcols.Remove, {"Index"}, {{"AllData", each _, type table [Index=number, Custom=text]}}),
        notesTable.ToList = Table.AddColumn(index.GroupBy, "Daily Notes", each [AllData][Daily Notes]),
        listValues.Extract = Table.TransformColumns(notesTable.ToList, {"Daily Notes", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
        notesTableCol.Remove = Table.RemoveColumns(listValues.Extract,{"AllData"}),
        notes.MergeBack = Table.NestedJoin(notesTableCol.Remove, {"Index"}, indexCol.Unpivot, {"Index"}, "Removed Columns1", JoinKind.LeftOuter),
        mergedData.Expand = Table.ExpandTableColumn(notes.MergeBack, "Removed Columns1", {"Attribute", "Value"}, {"Attribute", "Value"}),
        indexCol.RePivot = Table.Pivot(mergedData.Expand, List.Distinct(mergedData.Expand[Attribute]), "Attribute", "Value"),
        notesCol.MoveToEnd = Table.ReorderColumns(indexCol.RePivot,{"Index", "SAT", "SUN", "MON", "TUE", "WED", "THU", "FRI", "Daily Notes"}),
        indexCol.Remove = Table.RemoveColumns(notesCol.MoveToEnd,{"Index"})
    in
        indexCol.Remove

  3. #3
    Acolyte Nick Burns's Avatar
    Join Date
    May 2017
    Posts
    75
    Articles
    0
    Excel Version
    Office 365
    Thanks Bob...

    I was still doing my own Google searches before I saw your reply.
    The pivot / repivot may cause issues as I'm dealing with thousands of records.

    I did come up with a one-liner, though it doesn't include the Column reference:

    Code:
    = Table.AddColumn(Source, "Daily Notes", each Text.Combine(List.Select({[SAT],[SUN],[MON],[TUE],[WED],[THU],[FRI]}, each Value.Is(Value.FromText(_), type text)),","))
    Oh... by the way, YOU'RE WELCOME!

  4. #4
    Acolyte Nick Burns's Avatar
    Join Date
    May 2017
    Posts
    75
    Articles
    0
    Excel Version
    Office 365
    A little more research and I came up with the following using List.Accumulate.

    Two minor issues, but nothing critical unless there's a better way:
    1) I had to list the Column/Value pairs in reverse
    2) It leaves a trailing comma

    Code:
    = Table.AddColumn(Source, "Daily Notes", each List.Accumulate({{"FRI",[FRI]},{"THU",[THU]},{"WED",[WED]},{"TUE",[TUE]},{"MON",[MON]},{"SUN",[SUN]},{"SAT",[SAT]}},"",(state,current) => if Value.Is(Value.FromText(current{1}), type text) then "(" & current{0} & ") " & current{1} & "," & state else state))
    Oh... by the way, YOU'RE WELCOME!

  5. #5
    Acolyte Nick Burns's Avatar
    Join Date
    May 2017
    Posts
    75
    Articles
    0
    Excel Version
    Office 365
    I had to reverse my state & current concatenation, that fixes the pair list and then test for a blank state value to remove the beginning comma:

    Code:
    = Table.AddColumn(Source, "Daily Notes", each List.Accumulate({{"SAT",[SAT]},{"SUN",[SUN]},{"MON",[MON]},{"TUE",[TUE]},{"WED",[WED]},{"THU",[THU]},{"FRI",[FRI]}},"",(state,current) => if Value.Is(Value.FromText(current{1}), type text) then state & (if state = "" then "" else ",") & "(" & current{0} & ") " & current{1} else state))
    Oh... by the way, YOU'RE WELCOME!

  6. #6
    Acolyte cyborgski's Avatar
    Join Date
    May 2019
    Posts
    60
    Articles
    0
    Excel Version
    Office 365
    Here is another option for you Nick. The Text.Combine will ignore the nulls generated by the List.Transform AND handle the extra quote issue for the last item in the list.

    Regards,
    Mike

    Code:
    Table.AddColumn(Source, "Daily Notes", each Text.Combine(
    List.Transform({"SAT","SUN","MON","TUE","WED","THU","FRI"},
    (item) => 
    if  Value.Is(Value.FromText(Record.Field(_,item)), type text) 
    then "(" & item & ") " & Record.Field(_,item) else null),
    ", "))
    Last edited by cyborgski; 2019-10-29 at 09:00 AM.

  7. #7
    Acolyte Nick Burns's Avatar
    Join Date
    May 2017
    Posts
    75
    Articles
    0
    Excel Version
    Office 365
    Awesome cyborgski!

    See, there's always more than one way to get something done in PQ!
    Oh... by the way, YOU'RE WELCOME!

  8. #8
    Conjurer Bill Szysz's Avatar
    Join Date
    Apr 2016
    Location
    Poznań, Poland Dublin, Ireland
    Posts
    103
    Articles
    0
    Excel Version
    Excel 365
    Another alternative way ;-)
    Code:
    #"Added Custom" = Table.AddColumn(Source, "Daily Notes", each Text.Combine(List.Transform(Table.ToRows(Table.SelectRows(Record.ToTable(_), each [Value] is text)), each "(" & Text.Combine(_, ") ")), ", "))

  9. #9
    Acolyte Nick Burns's Avatar
    Join Date
    May 2017
    Posts
    75
    Articles
    0
    Excel Version
    Office 365
    Quote Originally Posted by Bill Szysz View Post
    Another alternative way ;-)
    Code:
    #"Added Custom" = Table.AddColumn(Source, "Daily Notes", each Text.Combine(List.Transform(Table.ToRows(Table.SelectRows(Record.ToTable(_), each [Value] is text)), each "(" & Text.Combine(_, ") ")), ", "))
    Bill - how does that command know what columns to use?
    Obviously my sample was just a subset of my actual data table.

    Thanks


    Sent from my iPhone using Tapatalk
    Oh... by the way, YOU'RE WELCOME!

  10. #10
    Acolyte cyborgski's Avatar
    Join Date
    May 2019
    Posts
    60
    Articles
    0
    Excel Version
    Office 365
    I enjoy when Bill posts because I always learn something (and I am from studying this post). But to your point Nick, I think you would need a Record.SelectFields to filter on only the fields that you are evaluating:

    Code:
    Table.AddColumn(Source, "Daily Notes", each   Text.Combine(List.Transform(Table.ToRows(Table.SelectRows(Record.ToTable(Record.SelectFields(_, {"SAT","SUN","MON","TUE","WED","THU","FRI"})), each [Value] is text)), each "(" & Text.Combine(_, ") ")), ", "))
    I am sure that I will be corrected but this line is getting a little long ...

Page 1 of 2 1 2 LastLast

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
  •