In Power Query , how to Extract values from many Arrays columns

[Nazdac911]

Hi all 

I have this excel file (as an example) ( the original data is much bigger and had 15 columns 

Arrays values in Age and Length columns are related .. 
I want inside power Query to extract these values and get the follwoing table : 

Wht are the steps in power Query to  implemnt that ! 
I looked up Youtube , and mostly the JSON columns are not formatted as a numaric array ! 
thanks in advance 
Regards 

[Nazdac911]

Thanka  alot for this solution 
I will accept this as a solution 
I tried it for my data and it did not work , but I think this is because of data corrupted , I keep reciving "Expression.Error: We cannot convert the value null to type Text" 😞 

[amitchandak · Accepted solution]

@Nazdac911 , convert them to list by replace [ -> { and ] -> }  and space with comma

then use List.Zip to zip the columns into a new column

Find the code below

Please check steps after custom column, attaching the file used

let
    Source = Excel.Workbook(File.Contents("C:\Amit Files\PBI two list.xlsx"), null, true),
    Data_Sheet = Source{[Item="Data",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Data_Sheet, [PromoteAllScalars=true]),
    #"Added Custom" = Table.AddColumn(#"Promoted Headers", "Zip", each let

_break = List.Zip( {Text.Split([Subject],","), Text.Split([Marks],",")} ),
_Trans = List.Transform(_break, each Record.FromList(_, {"Subject", "Marks"}))
in 
_Trans),
    #"Expanded Zip" = Table.ExpandListColumn(#"Added Custom", "Zip"),
    #"Expanded Zip1" = Table.ExpandRecordColumn(#"Expanded Zip", "Zip", {"Subject", "Marks"}, {"Zip.Subject", "Zip.Marks"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Zip1",{"Subject", "Marks"})
in
    #"Removed Columns"