Text.Combine if column contains text

Nick Burns

Nick Burns

Member
Joined
May 24, 2017
Messages
162
Reaction score
0
Points
16
Excel Version(s)
Office 365
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

DailyNotes.PNG

View attachment DailyNotes.xlsx
 
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
 
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)),","))
 
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))
 
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))
 
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:
Awesome cyborgski!

See, there's always more than one way to get something done in PQ!
 
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 Szysz said:
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(_, ") ")), ", "))
Click to expand...

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
 
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 ...
 
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"
 

Attachments

  • DailyNotes_BS.xlsx
    25.5 KB · Views: 10
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!
 
You must log in or register to reply here.
Back
Top