Forum Discussion
request for transformations for table in text
I woul like to request for help in creating two tables in desirable format ( steps in Power query):
1. BUI ( original in plain text) :
I need to achieve this view below:
The steps which I have to perform in PQ : spereate yellow roww starts with "B" on the left and move rows below start with "R" on the right after I need to fill down the table with yellow rows on the left.
Could you please help.
| B | 30 | RR001 | 147SAT17 | 10/17/2020 | Y | D | Yes | No | No | No | No | No | No | R | 147 | 2 | U | RR | RIVER ROAD GARAGE | 4.37 | T | Y | 0 | 0 | Y | |||
| B | 30 | RR001 | 147SAT17 | 10/17/2020 | Y | D | Yes | No | No | No | No | No | No | R | 147 | 2 | U | CTBSB | CANNING TOWN Bus Stn | 4.55 | T | Y | 0 | 4.8 | Y | |||
| B | 30 | RR001 | 147SAT17 | 10/17/2020 | Y | D | Yes | No | No | No | No | No | No | R | 147 | 2 | U | KHET | KEIR HARDIE EST Tarling Rd | 4.58 | T | Y | 0.47 | 0 | Y |
Mayny thanks,
Iwona
Hi Ivo_T
If you have only one yellow row, you can try the following code. To use this code, create a blank query, open its Advanced Editor and remove all existing codes there. Paste below code into the window and replace the Folder path with your text file's path. Save the code and you will see the result in below image.
let Source = Table.FromColumns({Lines.FromBinary(File.Contents("C:\Users\Admin\Desktop\Sample.txt"), null, null, 1252)}), // select the first row as a table and add an Index column to it LeftTable = Table.AddIndexColumn(Table.FirstN(Source,1),"Index",1), // select the other rows except the first row as a table and add an Index column to it RightTable = Table.AddIndexColumn(Table.Skip(Source,1),"Index",1), // merge two tables on Index column with all rows from RightTable CombineTable = Table.NestedJoin(LeftTable, {"Index"}, RightTable, {"Index"}, "Table", JoinKind.RightOuter), // expand the new "Table" column #"Expanded Table" = Table.ExpandTableColumn(CombineTable, "Table", {"Column1"}, {"Column1.1"}), // Remove Index column #"Removed Columns" = Table.RemoveColumns(#"Expanded Table",{"Index"}), // Fill Down Column1 #"Filled Down" = Table.FillDown(#"Removed Columns",{"Column1"}) in #"Filled Down"Then split two columns by comma. You will get the expected result you want.
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
3 Replies
You can use this to provide sample data
Hi Ivo_T
If you have only one yellow row, you can try the following code. To use this code, create a blank query, open its Advanced Editor and remove all existing codes there. Paste below code into the window and replace the Folder path with your text file's path. Save the code and you will see the result in below image.
let Source = Table.FromColumns({Lines.FromBinary(File.Contents("C:\Users\Admin\Desktop\Sample.txt"), null, null, 1252)}), // select the first row as a table and add an Index column to it LeftTable = Table.AddIndexColumn(Table.FirstN(Source,1),"Index",1), // select the other rows except the first row as a table and add an Index column to it RightTable = Table.AddIndexColumn(Table.Skip(Source,1),"Index",1), // merge two tables on Index column with all rows from RightTable CombineTable = Table.NestedJoin(LeftTable, {"Index"}, RightTable, {"Index"}, "Table", JoinKind.RightOuter), // expand the new "Table" column #"Expanded Table" = Table.ExpandTableColumn(CombineTable, "Table", {"Column1"}, {"Column1.1"}), // Remove Index column #"Removed Columns" = Table.RemoveColumns(#"Expanded Table",{"Index"}), // Fill Down Column1 #"Filled Down" = Table.FillDown(#"Removed Columns",{"Column1"}) in #"Filled Down"Then split two columns by comma. You will get the expected result you want.
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
