Power Query - Fill.Up Function

[Rana Gray]

Hey Everyone,

Is there a way to use the Fill.Up function to Text.Combine a particular column?

Scenario: I have duplicates stacked on top of each other, I want to fill the blank spaces with the data below unless updates were made on the new line. Where I'm stuck is the "Notes:" column. I don't want to exclude previous notes from the more recent notes - I want them to combine when the Fill.Up step happens but just for that column.

Hope someone can help I keep getting syntax errors.

 

[alex78]

Hi,

an attempt - Source is the previous step

    A = Table.ColumnNames(Source),
    B = List.Reverse(List.Transform(List.Accumulate(List.Reverse(Source[#"Notes:"]), {}, (s,c)=> s & {c ?? {Text.Combine(List.LastN(s, each Value.Type(_) <> List.Type), " - ")}}), each try _{0} otherwise _)),
    C = Table.FromColumns(List.ReplaceRange(Table.ToColumns(Source), List.PositionOf(A, "Notes:") ,1, {B}), A)

Regards

 

[GraH]

Looks like you want to remove duplicates, but keep all comments.

The pattern maybe something alike:

Cat	comment
a	blah
	bluh
	bleh
e	bloh
	blih
b	bluh
d
	oki
	doki

let
    Source = Excel.CurrentWorkbook(){[Name="data_sample"]}[Content],
    Auto_step_types = Table.TransformColumnTypes(Source,{{"Cat", type text}, {"comment", type text}}),
    Fill_down = Table.FillDown(Auto_step_types,{"Cat"}),
    Group_with_txt_combine = Table.Group(Fill_down, {"Cat"}, {{"Comments", each Text.Combine([comment], "#(lf)"), type nullable text}})
in
    Group_with_txt_combine

Cat	Comments
a	blah bluh bleh
e	bloh blih
b	bluh
d	oki doki