Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
Wave @Ashish_Mathur
Thank you
Douglas
You are welcome.
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
96 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |