Exending List in PowerQuery

[Anonymous]

Hi ,

Wonder if I can get any help on expanding this json result in response to a Post API the response is Json.

There are only 5 rows worth data here and on 3 List columns as per below. 1st and 3rd column has row values and 2nd column is cominbation of 7 field record.

Appreciate your help.
Basically I want a row each with list value for 1st column, expanded record fields of 2nd column and value of 3rd column.
Thank you.

[Anonymous · Accepted solution]

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":""}

[Mariusz · Accepted solution]

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.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[dax]

Hi @Anonymous , 

I am not clear about your requirement, you could try to expand these columns to see whether it work or not

By the way, you also could inform me your expected output or your sample data.

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous · Accepted solution]

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":""}

[Mariusz · Accepted solution]

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.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[Anonymous · Accepted solution]

Marvelous thank you very much @Mariusz you are a star