M Function to Fill Down depending on cell value

Power Query
1

Dear community, please enlighten me!

I want to fill down columns depending on the cell value in their first row.
The column should be only filled down, if the text inside the first row cell starts with “WS”.
The solution shouldn’t break if additional columns are added.

Big thanks for your help!

See the current work-in-progress PBIX-file.
fill down depending on cell value.pbix (79.6 KB)

2

Hi @aleph.niemeier,

Didn’t see your post until just now because you selected another category than Power Query (changed that now). In future keep in mind to also provide the xlsx data - thank you!

Give this fxCustomFillDown function a go:

( inputTable as table, rowNumber as number, startText as text ) as table =>
let
    colNames = Table.ColumnNames( inputTable ),
    fieldValues = Record.ToList( inputTable{rowNumber-1} ),
    findPositions = List.PositionOf( List.Transform( fieldValues, each Text.Start( _, Text.Length( startText))), startText, Occurrence.All ),
    listCols = List.Transform( findPositions, each colNames{_} ),
    fillDown = Table.FillDown( inputTable, listCols )
in
    if (rowNumber = 0 or rowNumber > List.Count( colNames )) 
    then error "A one-based row index number is required." 
    else fillDown

To illustrate, depicted here it’s invocation and partial result:

image
image838×217 9.82 KB

Here’s your sample file.
fill down depending on cell value.pbix (140.6 KB)

I hope this is helpful

2 Likes
3

In my opinion, @Melissa consistently offers effective solutions with highly informative code. I find working with Melissa’s methods in M language enjoyable.
Nevertheless, I see your question as a chance to assess my abilities. Here’s how I approached the challenge:

= Table.Transpose(Table.Combine(List.Transform(Table.Split(Table.Transpose(CopyOfTable),1),each Table.Transpose(Table.FillDown(Table.Transpose(_),{"Column1"})))))