How do I turn these columns into rows

[CBVC-HB]

I have a csv in the format:

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

Inserted this into PowerBi and trying to transform it to this, need those columns to go into a new row with the same cap code.

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

Not sure how to do this. Any help would be appreciated.

[v-jingzhang]

Hi @CBVC-HB 

This is my solution. Hope it helps. 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Zc67EcAgDAPQXVxTIOVfJhmDY/81YpmjcQrxEb53tGa3FePqy1Zr9Q26e5bZn6OPEc8+e9TxcGjGc1kvzZ7MyYNAZBGhyMQPhVSIxXDf5DL+KZfZjTPl8udSLuXS3f4B", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"CAP Code" = _t, Term = _t, Mileage = _t, Finance = _t, Service = _t, Total = _t, Term.1 = _t, Mileage.1 = _t, Finance.1 = _t, Service.1 = _t, Total.1 = _t, Term.2 = _t, Mileage.2 = _t, Finance.2 = _t, Service.2 = _t, Total.2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"CAP Code", type text}, {"Term", Int64.Type}, {"Mileage", Int64.Type}, {"Finance", Int64.Type}, {"Service", Int64.Type}, {"Total", Int64.Type}, {"Term.1", Int64.Type}, {"Mileage.1", Int64.Type}, {"Finance.1", Int64.Type}, {"Service.1", Int64.Type}, {"Total.1", Int64.Type}, {"Term.2", Int64.Type}, {"Mileage.2", Int64.Type}, {"Finance.2", Int64.Type}, {"Service.2", Int64.Type}, {"Total.2", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"CAP Code"}, {{"AllData", each _, type table [CAP Code=nullable text, Term=nullable number, Mileage=nullable number, Finance=nullable number, Service=nullable number, Total=nullable number, Term.1=nullable number, Mileage.1=nullable number, Finance.1=nullable number, Service.1=nullable number, Total.1=nullable number, Term.2=nullable number, Mileage.2=nullable number, Finance.2=nullable number, Service.2=nullable number, Total.2=nullable number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each List.Split(List.Skip(List.First(Table.ToRows([AllData])),1),5)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"AllData"}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Removed Columns", "Custom"),
    #"Extracted Values" = Table.TransformColumns(#"Expanded Custom", {"Custom", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "Custom", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Custom.1", "Custom.2", "Custom.3", "Custom.4", "Custom.5"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Custom.1", Int64.Type}, {"Custom.2", Int64.Type}, {"Custom.3", Int64.Type}, {"Custom.4", Int64.Type}, {"Custom.5", Int64.Type}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Custom.1", "Term"}, {"Custom.2", "Mileage"}, {"Custom.3", "Finance"}, {"Custom.4", "Service"}, {"Custom.5", "Total"}})
in
    #"Renamed Columns"

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[ppm1 · Accepted solution]

Please see the "repeating columns" part of this video.

https://www.youtube.com/watch?v=huT1fDJh0zU

Pat