Forum Discussion

DouglasBrito's avatar
DouglasBrito
Icon for Helper I rankHelper I
5 years ago
Solved

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

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...
  • v-jingzhang's avatar
    v-jingzhang
    5 years ago

    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:

    11042.jpg

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

    11043.jpg

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

    11044.jpg11045.jpg

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

    11046.jpg

    Finally, rename the columns and reorder them.

    11047.jpg

    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's avatar
Ashish_Mathur
Icon for Super User rankSuper User
5 years ago

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.

Untitled.png