request for transformations for table in text

[Ivo_T]

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

[v-jingzhang · Accepted solution]

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.

[Anonymous]

the thing you ask can be done with a group by followed by a merge.

but to allow someone to help you, you should publish the source table so that it is copyable.
You should also explain if lines with prefix B and those prefixed R are in different numeor what needs to be done ...

[Vijay_A_Verma]

You can use this to provide sample data

https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...