Exending List in PowerQuery

Thanks Zoe, I tried expanding the columns 2 list columns will expand to 5 records each with repeats and record field in the middle can't be extended.

Basically what I want to do is to parse this JSON to a data table below.

"MeasDataTypes":[10,10,10,10,10],

    "MeasDateTimes":[

        {"Day":1,"HasBeenSet":true,"Hour":0,"Minute":0,"Month":1,"Second":0,"Year":2020},

        {"Day":2,"HasBeenSet":true,"Hour":0,"Minute":0,"Month":1,"Second":0,"Year":2020},

        {"Day":3,"HasBeenSet":true,"Hour":0,"Minute":0,"Month":1,"Second":0,"Year":2020},

        {"Day":4,"HasBeenSet":true,"Hour":0,"Minute":0,"Month":1,"Second":0,"Year":2020},

        {"Day":5,"HasBeenSet":true,"Hour":0,"Minute":0,"Month":1,"Second":0,"Year":2020}

        ],

        "MeasInfos":null,

        "MeasValues":[4.1,8.6,9.8,5.6,7.7],"MeasureUnit":""}

Hi Anonymous 

Please see the attached file with a solution or the below script, the main transformation takes place in Added Custom step.

let
    Source = Json.Document(File.Contents("C:\Users\mariu\OneDrive\Desktop\test.json")),
    #"Converted to Table" = Record.ToTable(Source),
    #"Filtered Rows" = Table.SelectRows(#"Converted to Table", each ([Value] <> null and [Value] <> "")),
    #"Transposed Table" = Table.Transpose(#"Filtered Rows"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
    #"Added Custom" = Table.AddColumn(#"Promoted Headers", "Custom", (a) => let 
                countRows = List.Max ( 
                    { 
                        List.Count( a[MeasDataTypes] ), 
                        List.Count( a[MeasDateTimes] ), 
                        List.Count( a[MeasValues] ) 
                    } 
                ) -1,
                iList = { 0..countRows },
                transform = List.Transform( 
                    iList,  
                    (b) => [
                        MeasDataTypes = a[MeasDataTypes]{b}?, 
                        MeasDateTimes = a[MeasDateTimes]{b}?,
                        MeasValues = a[MeasValues]{b}?
                    ] 
                )
            in transform),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Removed Other Columns", "Custom"),
    #"Expanded Custom1" = Table.ExpandRecordColumn(#"Expanded Custom", "Custom", {"MeasDataTypes", "MeasDateTimes", "MeasValues"}, {"MeasDataTypes", "MeasDateTimes", "MeasValues"}),
    #"Expanded MeasDateTimes" = Table.ExpandRecordColumn(#"Expanded Custom1", "MeasDateTimes", {"Day", "HasBeenSet", "Hour", "Minute", "Month", "Second", "Year"}, {"MeasDateTimes.Day", "MeasDateTimes.HasBeenSet", "MeasDateTimes.Hour", "MeasDateTimes.Minute", "MeasDateTimes.Month", "MeasDateTimes.Second", "MeasDateTimes.Year"})
in
    #"Expanded MeasDateTimes"

Let me know if you need any help.

Marvelous thank you very much Mariusz you are a star