Adding a column for multiple rows based on a top dynamic row content

Hello @DouglasBrito

Several steps are needed to achieve this goal. Please try the steps below.

Supposing you first have a table like this in Query Editor:

Replace the blank values with null in Column2 and Column3: Transform -> Replace Values.

Add a custom column and use Fill to get the names of the cardholders for each row.

Select Column2 and filter the rows with the Value Record Date and Null.

Finally, rename the columns and reorder them.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8srPyFPSUQKhWJ1opYDUosz8FAXXvBSgSEB+cUlmXrqCS2JJKpAbUpSYB2GDVBoaGgLFjIyMgKSxsTFJYo55eYl0txSbmG9iSUZq+aBwCoZYLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type","",null,Replacer.ReplaceValue,{"Column2", "Column3"}),
    #"Added Custom" = Table.AddColumn(#"Replaced Value", "Custom", each if [Column2] = null and [Column3] = null and [Column1] <> null then [Column1] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Custom"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Column2] = "222")),
    #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{"Column1", "Period End"}, {"Column2", "Posting Date"}, {"Column3", "Tran Date"}, {"Custom", "Card Holder"}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"Card Holder", "Period End", "Posting Date", "Tran Date"})
in
    #"Reordered Columns"

You can download the .pbix file for more details. I hope this helps.

Best regards

Community Support Team _ Jing Zhang

If this post helps,please consider Accepting it as the solution to help other members find it.