Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
DouglasBrito
Helper I
Helper I

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 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

1 ACCEPTED SOLUTION
v-jingzhang
Community Support
Community Support

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.

View solution in original post

4 REPLIES 4
DouglasBrito
Helper I
Helper I

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

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
v-jingzhang
Community Support
Community Support

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
Super User
Super User

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.