How do I turn these columns into rows

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.