Need help transforming a spreadsheet how I want using power query so I can automate it

[CBVC-HB]

Hi Guys,

I have a csv in the below format (just a small excerpt, actually goes up to 30000 for 2nd row, after which it repeats with 36 on the first row and 5000 to 30000 on 2nd row):

	24
	5000			8000			10000
CAP Code	Finance	Service	Total	Finance	Service	Total	Finance	Service	Total
A	1	2	3	4	5	6	7	8	9
B	11	12	13	14	15	16	17	18	19
C	21	22	23	24	25	26	27	28	29

I want to put it into this format:

CAP Code	Term	Mileage	Finance	Service	Total
A	24	5000	1	2	3
A	24	8000	4	5	6
A	24	10000	7	8	9
B	24	5000	11	12	13
B	24	8000	14	15	16
B	24	10000	17	18	19
C	24	5000	21	22	23
C	24	8000	24	25	26
C	24	10000	27	28	29

Having trouble doing this, it's feels like a transpose but with extra steps. Hope someone can point me in the right direction.

[CBVC-HB]

Is there any way of doing this through the GUI? I was working on it earlier and was able to change it to:

CAP Code	Term	Mileage	Finance	Service	Total	Term	Mileage	Finance	Service	Total	Term	Mileage	Finance	Service	Total
A	24	5000	1	2	3	24	8000	4	5	6	24	10000	7	8	9
B	24	5000	11	12	13	24	8000	14	15	16	24	10000	17	18	19
C	24	5000	21	22	23	24	8000	24	25	26	24	10000	27	28	29

So it's like I need column 7-11 to move onto a new row while keeping it's cap code on the first column, and doing it every 5 columns until the end.

[slorin]

Hi

Another solution 

let
Source = YourSource,
Transpose = Table.Transpose(Source),
FillDown = Table.FillDown(Transpose,{"Column1", "Column2"}),
Headers = Table.PromoteHeaders(FillDown, [PromoteAllScalars=true]),
ColumnNames = Table.RenameColumns(Headers,{{"Column1", "Term"}, {"Column2", "Mileage"}, {"CAP Code", "Code"}}),
UnPivot = Table.UnpivotOtherColumns(ColumnNames, {"Term", "Mileage", "Code"}, "CAP Code", "Value"),
Pivot = Table.Pivot(UnPivot, List.Distinct(UnPivot[#"Code"]), "Code", "Value")
in
Pivot

Stéphane 

[wdx223_Daniel]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nZA/C4MwEEe/SsjskDurtqMNdC60mziIOghFoUg/f+9FCnbp0OEX8ue9XC5N453PvB5scD/TZhtahBB2B8fvpYT9GifWVxeXYbSNyzR3c8/sNj5fU5rdl7V7/H1GgZqy9GDJLbRSWEpLxQstp0SeIUEFVoAFWsAFXhAEQzYlcnG6HUXzz18piqIoiqKoKe0b", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t, Column8 = _t, Column9 = _t, Column10 = _t]),
    Custom1 = let a=Table.ToColumns(Table.Skip(Source,2)),b=List.Split(List.Skip(a),3) in Table.FillDown(Table.Combine(List.Transform(List.Positions(b),each Table.PromoteHeaders(Table.FromColumns({a{0},{"Term",Source[Column2]{0}},{"Mileage",Record.ToList(Source{1}){_*3+1}}}&b{_})))),{"Term","Mileage"})
in
    Custom1