Text.Combine if column contains text

[Nick Burns]

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



View attachment DailyNotes.xlsx

 

[Bob Phillips]

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

 

[Nick Burns]

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:

= Table.AddColumn(Source, "Daily Notes", each Text.Combine(List.Select({[SAT],[SUN],[MON],[TUE],[WED],[THU],[FRI]}, each Value.Is(Value.FromText(_), type text)),","))

 

[Nick Burns]

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

= 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))

 

[Nick Burns]

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:

= 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))

 

[cyborgski]

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

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),
", "))

 

[Nick Burns]

Awesome cyborgski!

See, there's always more than one way to get something done in PQ!

 

[Bill Szysz]

Another alternative way ;-)

#"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(_, ") ")), ", "))

 

[Nick Burns]

Another alternative way ;-)

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

 

[cyborgski]

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:

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

 

[Bill Szysz]

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:

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"

 

[Nick Burns]

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!