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

[DouglasBrito]

Hi there,

I'm working with a card statement file that has the layout  as current layout. I need to see if it is possible to come up with the desired layout. 

Basically the card holder name is not being displayed to all transaction lines, it is coming as a header row. But I need to have it properly linked to the transaction lines.

Thanks
Current Layout

John
Period End,Posting Date,Tran Date
111,222,333,444
111,222,333,444
111,222,333,444
Anna
Period End,Posting Date,Tran Date
111,222,333,444
111,222,333,444
111,222,333,444
111,222,333,444
Mathew
Period End,Posting Date,Tran Date
111,222,333,444
111,222,333,444
111,222,333,444
111,222,333,444
111,222,333,444
111,222,333,444

Desired Output

Card Holder, Period End,Posting Date,Tran Date
John, 111,222,333,444
John,111,222,333,444
John,111,222,333,444
Anna, 111,222,333,444
Anna, 111,222,333,444
Anna, 111,222,333,444
Anna, 111,222,333,444
Mathew,111,222,333,444
Mathew,111,222,333,444
Mathew,111,222,333,444
Mathew,111,222,333,444
Mathew,111,222,333,444
Mathew,111,222,333,444

[DouglasBrito]

Wave @Ashish_Mathur

Thanks very much for your clever approach for this, it worked fine. Sorry for the delay on my answer.

Thank you

Douglas

[Ashish_Mathur]

You are welcome.

[v-jingzhang · Accepted solution]

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.

[Ashish_Mathur]

Hello

This M code works

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8srPyFOK1YlWCkgtysxPUXDNS9EJyC8uycxLV3BJLEnVCSlKzAOzwKoMDQ11jIyMdIyNjXVMTExIEnPMy0ukk1XYxHwTSzJSywfQAUSJxQIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Header = _t]),
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
    #"Uppercased Text" = Table.TransformColumns(#"Added Index",{{"Header", Text.Upper, type text}}),
    #"Added Custom" = Table.AddColumn(#"Uppercased Text", "Custom", each Text.Start([Header],6)),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Card Holder", each try if #"Added Custom" [Custom] {[Index] + 1}="PERIOD" then [Custom] else null otherwise null),
    #"Filled Down" = Table.FillDown(#"Added Custom1",{"Card Holder"}),
    #"Added Custom2" = Table.AddColumn(#"Filled Down", "Rows to remove", each [Custom]=[Card Holder]),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom2", each ([Rows to remove] = false) and ([Custom] = "111,22")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Index", "Custom", "Rows to remove"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Columns", "Header", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Header.1", "Header.2", "Header.3", "Header.4"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Header.1", Int64.Type}, {"Header.2", Int64.Type}, {"Header.3", Int64.Type}, {"Header.4", Int64.Type}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Changed Type",{"Card Holder", "Header.1", "Header.2", "Header.3", "Header.4"}),
    #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Header.1", "Period End"}, {"Header.2", "Posting Date"}, {"Header.3", "Tran Date"}})
in
    #"Renamed Columns"

I hope this helps.