Solved: Power Query Nested JSON (List -> List of Record...

karhus01 · ‎01-04-2021

Hello,

I have been struggling for a long time can I can't seem to get the table that I want to. I'm using Power BI and Power Query to get a table to be used in a visual.

I have this raw data into the table:

Title	ActivityStatus
Item 1	[{"Kommersialisering":false},{"Marknadsstrategi":false},{"Produktutveckling & testning":false},{"Konceptutveckling":true},{"Produktlansering":false},{"Affärsanalys":false},{"Bedömning":true}]
Item 2	[{"Konceptutveckling":true},{"Kommersialisering":false},{"Produktlansering":false},{"Affärsanalys":true},{"Bedömning":true},{"Produktutveckling & testning":false},{"Marknadsstrategi":true}]
Item 3	[{"Affärsanalys":false},{"Bedömning":true},{"Marknadsstrategi":false},{"Kommersialisering":false},{"Produktutveckling & testning":false},{"Konceptutveckling":false},{"Produktlansering":false}]

What I want as a result is the following:

Title	Kommersialisering	Marknadsstrategi	Produktutveckling & Testning	Konceptutveckling	Produktlansering	Affärsanalys	Bedömning
Item 1	false	false	false	true	false	false	true
Item 2	false	true	false	true	false	true	true
Item 3	false	false	false	false	false	false	true

When using the designer and Transform JSON -> ExpandListColumn-> ExpandRecordColumn I get duplicate rows, one for each in the resulting ExpandListColumn. See example below:

Title	Kommersialisering	Marknadsstrategi	Produktutveckling & Testning	Konceptutveckling	Produktlansering	Affärsanalys	Bedömning
Item 1	false	null	null	null	null	null	null
Item 1	null	false	null	null	null	null	null
Item 1	null	null	false	null	null	null	null
Item 1	null	null	null	true	null	null	null
Item 1	null	null	null	null	false	null	null
Item 1	null	null	null	null	null	true	null
Item 1	null	null	null	null	null	null	true

Can anyone guide me on what I'm doing wrong and provide a Power Query that can result in the the table that I want?

I have tried the following queries without any luck, I'm just getting more and more confused...

First attempt:

let
    Källa = SharePoint.Tables("sharepointURL", [Implementation=null, ApiVersion=15]),
    #"GUID" = Källa{[Id="GUID"]}[Items],
    #"Omdöpta kolumner" = Table.RenameColumns(#"GUID",{{"ID", "ID.1"}}),
    #"Analyserad JSON" = Table.TransformColumns(#"Omdöpta kolumner",{{"ActivitesStatus", Json.Document}}),
    #"Borttagna kolumner" = Table.RemoveColumns(#"Analyserad JSON",{"FileSystemObjectType", "Id", "ServerRedirectedEmbedUri", "ServerRedirectedEmbedUrl", "ContentTypeId", "ComplianceAssetId", "PredecessorsId", "Priority", "Status", "Body", "StartDate", "DueDate", "Checkmark", "RelatedItems", "PreviouslyAssignedToStringId", "Activity", "ID.1", "Modified", "Created", "AuthorId", "EditorId", "OData__UIVersionString", "Attachments", "GUID", "FirstUniqueAncestorSecurableObject", "RoleAssignments", "AttachmentFiles", "ContentType", "GetDlpPolicyTip", "FieldValuesAsHtml", "FieldValuesAsText", "FieldValuesForEdit", "File", "Folder", "LikedByInformation", "ParentList", "Properties", "Versions", "Predecessors", "ParentID", "PreviouslyAssignedTo", "Author", "Editor", "Assigned To"}),
    #"Expanderad ActivitesStatus" = Table.ExpandListColumn(#"Borttagna kolumner", "ActivitesStatus"),
    #"Expanderad ActivitesStatus1" = Table.ExpandRecordColumn(#"Expanderad ActivitesStatus", "ActivitesStatus", {"Kommersialisering", "Marknadsstrategi", "Produktutveckling & testning", "Konceptutveckling", "Produktlansering", "Affärsanalys", "Bedömning"}, {"Kommersialisering", "Marknadsstrategi", "Produktutveckling & testning", "Konceptutveckling", "Produktlansering", "Affärsanalys", "Bedömning"}),
    #"RemoveNull1" = Table.TransformColumns(#"Expanderad ActivitesStatus1",
        {
            {"Kommersialisering", each if _ = null then false else _},
            {"Marknadsstrategi", each if _ = null then false else _},
            {"Produktutveckling & testning", each if _ = null then false else _},
            {"Konceptutveckling", each if _ = null then false else _},
            {"Produktlansering", each if _ = null then false else _},
            {"Affärsanalys", each if _ = null then false else _},
            {"Bedömning", each if _ = null then false else _}
        }),
in
    #"RemoveNull1"

Second attempt:

let
    Källa = SharePoint.Tables("SharepointURL", [Implementation=null, ApiVersion=15]),
    #"GUID" = Källa{[Id="GUID"]}[Items],
    #"Omdöpta kolumner" = Table.RenameColumns(#"GUID",{{"ID", "ID.1"}}),
    #"Analyserad JSON" = Table.TransformColumns(#"Omdöpta kolumner",{{"ActivitesStatus", Json.Document}}),
    #"Borttagna kolumner" = Table.RemoveColumns(#"Analyserad JSON",{"FileSystemObjectType", "Id", "ServerRedirectedEmbedUri", "ServerRedirectedEmbedUrl", "ContentTypeId", "ComplianceAssetId", "PredecessorsId", "Priority", "Status", "Body", "StartDate", "DueDate", "Checkmark", "RelatedItems", "PreviouslyAssignedToStringId", "Activity", "ID.1", "Modified", "Created", "AuthorId", "EditorId", "OData__UIVersionString", "Attachments", "GUID", "FirstUniqueAncestorSecurableObject", "RoleAssignments", "AttachmentFiles", "ContentType", "GetDlpPolicyTip", "FieldValuesAsHtml", "FieldValuesAsText", "FieldValuesForEdit", "File", "Folder", "LikedByInformation", "ParentList", "Properties", "Versions", "Predecessors", "ParentID", "PreviouslyAssignedTo", "Author", "Editor", "Assigned To"}),
    KommersialiseringTabell = Table.FromList(Table.Column(Table.SelectRows(Table.ExpandRecordColumn(Table.ExpandListColumn(#"Borttagna kolumner", 
        "ActivitesStatus"), 
            "ActivitesStatus", 
            {"Kommersialisering"}, 
            {"Kommersialisering"}
            ), 
            each [Kommersialisering] = true), "Title"), null, {"Kommercialisering"}),
    MarknadsstrategiTabell = Table.FromList(Table.Column(Table.SelectRows(Table.ExpandRecordColumn(Table.ExpandListColumn(#"Borttagna kolumner", 
    "ActivitesStatus"), 
        "ActivitesStatus", 
        {"Marknadsstrategi"}, 
        {"Marknadsstrategi"}
        ), 
        each [Marknadsstrategi] = true), "Title"), null, {"Marknadsstrategi"}),
    ProduktutvecklingTestningTabell = Table.FromList(Table.Column(Table.SelectRows(Table.ExpandRecordColumn(Table.ExpandListColumn(#"Borttagna kolumner", 
    "ActivitesStatus"), 
        "ActivitesStatus", 
        {"Produktutveckling & testning"}, 
        {"Produktutveckling & testning"}
        ), 
        each [Produktutveckling_x0020__x0026__x0020_testning] = true), "Title"), null, {"Produktutveckling & testning"}),
    KonceptutvecklingTabell = Table.FromList(Table.Column(Table.SelectRows(Table.ExpandRecordColumn(Table.ExpandListColumn(#"Borttagna kolumner", 
    "ActivitesStatus"), 
        "ActivitesStatus", 
        {"Konceptutveckling"}, 
        {"Konceptutveckling"}
        ), 
        each [Konceptutveckling] = true), "Title"), null, {"Konceptutvecklin"}),
    ProduktlanseringTabell = Table.FromList(Table.Column(Table.SelectRows(Table.ExpandRecordColumn(Table.ExpandListColumn(#"Borttagna kolumner", 
    "ActivitesStatus"), 
        "ActivitesStatus", 
        {"Produktlansering"}, 
        {"Produktlansering"}
        ), 
        each [Produktlansering] = true), "Title"), null, {"Produktlansering"}),
    AffärsanalysTabell = Table.FromList(Table.SelectRows(Table.ExpandRecordColumn(Table.ExpandListColumn(#"Borttagna kolumner", 
    "ActivitesStatus"), 
        "ActivitesStatus", 
        {"Affärsanalys"}, 
        {"Affärsanalys"}
        ), 
        each [Affärsanalys] = true), null, {"Affärsanalys"}),
    BedömningTabell = Table.FromList(Table.Column(Table.SelectRows(Table.ExpandRecordColumn(Table.ExpandListColumn(#"Borttagna kolumner", 
    "ActivitesStatus"), 
        "ActivitesStatus", 
        {"Bedömning"}, 
        {"Bedömning"}
        ), 
        each [Bedömning] = true), "Title"), null, {"Bedömning"}),
    ResultsTable = #table(
        {
            "Bedömning", 
            "Konceptutveckling",
            "Marknadsstrategi", 
            "Affärsanalys",
            "Produktutveckling & testning", 
            "Kommercialisering",
            "Produktlansering"
        }, {})    
in
    ResultsTable

Third attempt:

let
    Källa = SharePoint.Tables("SharepointURL", [Implementation=null, ApiVersion=15]),
    #"GUID" = Källa{[Id="GUID"]}[Items],
    #"Omdöpta kolumner" = Table.RenameColumns(#"GUID",{{"ID", "ID.1"}}),
    #"Analyserad JSON" = Table.TransformColumns(#"Omdöpta kolumner",{{"ActivitesStatus", Json.Document}}),
    #"Borttagna kolumner" = Table.RemoveColumns(#"Analyserad JSON",{"FileSystemObjectType", "Id", "ServerRedirectedEmbedUri", "ServerRedirectedEmbedUrl", "ContentTypeId", "ComplianceAssetId", "PredecessorsId", "Priority", "Status", "Body", "StartDate", "DueDate", "Checkmark", "RelatedItems", "PreviouslyAssignedToStringId", "Activity", "ID.1", "Modified", "Created", "AuthorId", "EditorId", "OData__UIVersionString", "Attachments", "GUID", "FirstUniqueAncestorSecurableObject", "RoleAssignments", "AttachmentFiles", "ContentType", "GetDlpPolicyTip", "FieldValuesAsHtml", "FieldValuesAsText", "FieldValuesForEdit", "File", "Folder", "LikedByInformation", "ParentList", "Properties", "Versions", "Predecessors", "ParentID", "PreviouslyAssignedTo", "Author", "Editor", "Assigned To"}),
    KommersialiseringTabell = Table.FromList(Table.Column(Table.SelectRows(Table.ExpandRecordColumn(Table.ExpandListColumn(#"Borttagna kolumner", 
        "ActivitesStatus"), 
            "ActivitesStatus", 
            {"Kommersialisering"}, 
            {"Kommersialisering"}
            ), 
            each [Kommersialisering] = true), "Title"), null, {"Kommercialisering"}),
    MarknadsstrategiTabell = Table.FromList(Table.Column(Table.SelectRows(Table.ExpandRecordColumn(Table.ExpandListColumn(#"Borttagna kolumner", 
    "ActivitesStatus"), 
        "ActivitesStatus", 
        {"Marknadsstrategi"}, 
        {"Marknadsstrategi"}
        ), 
        each [Marknadsstrategi] = true), "Title"), null, {"Marknadsstrategi"}),
    ProduktutvecklingTestningTabell = Table.FromList(Table.Column(Table.SelectRows(Table.ExpandRecordColumn(Table.ExpandListColumn(#"Borttagna kolumner", 
    "ActivitesStatus"), 
        "ActivitesStatus", 
        {"Produktutveckling & testning"}, 
        {"Produktutveckling & testning"}
        ), 
        each [Produktutveckling_x0020__x0026__x0020_testning] = true), "Title"), null, {"Produktutveckling & testning"}),
    KonceptutvecklingTabell = Table.FromList(Table.Column(Table.SelectRows(Table.ExpandRecordColumn(Table.ExpandListColumn(#"Borttagna kolumner", 
    "ActivitesStatus"), 
        "ActivitesStatus", 
        {"Konceptutveckling"}, 
        {"Konceptutveckling"}
        ), 
        each [Konceptutveckling] = true), "Title"), null, {"Konceptutveckling"}),
    ProduktlanseringTabell = Table.FromList(Table.Column(Table.SelectRows(Table.ExpandRecordColumn(Table.ExpandListColumn(#"Borttagna kolumner", 
    "ActivitesStatus"), 
        "ActivitesStatus", 
        {"Produktlansering"}, 
        {"Produktlansering"}
        ), 
        each [Produktlansering] = true), "Title"), null, {"Produktlansering"}),
    AffärsanalysTabell = Table.FromList(Table.SelectRows(Table.ExpandRecordColumn(Table.ExpandListColumn(#"Borttagna kolumner", 
    "ActivitesStatus"), 
        "ActivitesStatus", 
        {"Affärsanalys"}, 
        {"Affärsanalys"}
        ), 
        each [Affärsanalys] = true), null, {"Affärsanalys"}),
    BedömningsTabell = Table.FromList(Table.Column(Table.SelectRows(Table.ExpandRecordColumn(Table.ExpandListColumn(#"Borttagna kolumner", 
    "ActivitesStatus"), 
        "ActivitesStatus", 
        {"Bedömning"}, 
        {"Bedömning"}
        ), 
        each [Bedömning] = true), "Title"), null, {"Bedömningstabel"}),
    ResultsTable = Table.Combine({BedömningsTabell, KonceptutvecklingTabell, MarknadsstrategiTabell, AffärsanalysTabell, ProduktutvecklingTestningTabell, KommersialiseringTabell, ProduktlanseringTabell})
in
    ResultsTable

CNENFRNL · ‎01-04-2021

Hi, @karhus01 , you might want to use the following code to achieve your goal,

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("pZExDoJAEEWvQramQEws7LQzxMQeKCYwkA27i9mdNTHG23gGL8DF3IIQQIIQ6z/z578/ccxOhNLbMJ/Fj4RFtZSoDQfBDWquyoTtCxAGn75Tz6ArBbkxpIGw5APxouvcVmTphlkl3KqX2CAIdx6hITW2imqV4bU37VTSdmAlQE2EOBRF89IGFIi7GShHzJu3VD2zlKV+Sxh2hDOH5/FXxeo8v1Otb2ui+DHftuVbU8/Pny7q44+PL+jWIaYf", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Title = _t, ActivityStatus = _t]),

    #"Json Column" = Table.TransformColumns(Source,{{"ActivityStatus", each Table.PromoteHeaders(Table.Transpose(Record.ToTable(Record.Combine(Json.Document(_)))), [PromoteAllScalars=true])}}),
    #"Expanded ActivityStatus" = Table.ExpandTableColumn(#"Json Column", "ActivityStatus", {"Kommersialisering", "Marknadsstrategi", "Produktutveckling & testning", "Konceptutveckling", "Produktlansering", "Affärsanalys", "Bedömning"}, {"Kommersialisering", "Marknadsstrategi", "Produktutveckling & testning", "Konceptutveckling", "Produktlansering", "Affärsanalys", "Bedömning"})
in
    #"Expanded ActivityStatus"

Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

View solution in original post

Syndicate_Admin · ‎10-22-2021

Hello, good afternoon

I am struggling with JSON that I try to transform into Power BI I have read that it can be done with Power Query but I do not know anything about programming, the JSON comes from an application to conduct surveys for example:

[
{
"question_answers": [
{
"QuestionId": "16306365464751",
"Question": "How likely are you to recommend your team change experience to a colleague?"
"Answer": "10",
"type": "range"
},
{
"QuestionId": "16306366720030",
"Question": "How easy was it to make the change of your team?",
"Answer": "5",
"type": "range"
},
{
"QuestionId": "16306365464752",
"Question": "How do you rate your overall experience?"
"Answer": 5,
"type": "webform_rating"
},
{
"QuestionId": "16312899762243",
"Question": "Why do you give us this rating?",
"Answer": "The person who helped me with the change of my team was very kind and the change was very fast",
"type": "textfield"
},
{
"QuestionId": "16327629594800",
"Question": "RollOut Type (HP/MAC/OTHER)",
"Answer": "HP",
"type": "hidden"
}
],
"submitted_on": "2021-10-12T15:33:06.479Z",
"submitted_by": "0",
"spent_time": 49
}
]

The problem is that Power BI doesn't translate it properly:

Create for each question a record when it should look like this:

Questions as header,

The power query ends like this:

Let
Source = Json.Document(File.Contents("C:\Users\a0m0plu\Downloads\response_1633623033932.json")),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"question_answers", "submitted_on", "submitted_by", "spent_time"}, {"question_answers", "submitted_on", "submitted_by", "spent_time"}),
#"Expanded question_answers" = Table.ExpandListColumn(#"Expanded Column1", "question_answers"),
#"Expanded question_answers1" = Table.ExpandRecordColumn(#"Expanded question_answers", "question_answers", {"QuestionId", "Question", "Answer", "type"}, {"question_answers. QuestionId", "question_answers. Question", "question_answers. Answer", "question_answers.type"}),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded question_answers1",{{"question_answers. QuestionId", Int64.Type}, {"question_answers. Question", type text}, {"question_answers. Answer", type any}, {"question_answers.type", type text}, {"submitted_on", type datetime}, {"submitted_by", Int64.Type}, {"spent_time", Int64.Type}})
in
#"Changed type"

Someone has some manual or video tutorial to be able to understand this or a solution,

Thank you and regards.

CNENFRNL · ‎01-04-2021

Hi, @karhus01 , you might want to use the following code to achieve your goal,

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("pZExDoJAEEWvQramQEws7LQzxMQeKCYwkA27i9mdNTHG23gGL8DF3IIQQIIQ6z/z578/ccxOhNLbMJ/Fj4RFtZSoDQfBDWquyoTtCxAGn75Tz6ArBbkxpIGw5APxouvcVmTphlkl3KqX2CAIdx6hITW2imqV4bU37VTSdmAlQE2EOBRF89IGFIi7GShHzJu3VD2zlKV+Sxh2hDOH5/FXxeo8v1Otb2ui+DHftuVbU8/Pny7q44+PL+jWIaYf", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Title = _t, ActivityStatus = _t]),

    #"Json Column" = Table.TransformColumns(Source,{{"ActivityStatus", each Table.PromoteHeaders(Table.Transpose(Record.ToTable(Record.Combine(Json.Document(_)))), [PromoteAllScalars=true])}}),
    #"Expanded ActivityStatus" = Table.ExpandTableColumn(#"Json Column", "ActivityStatus", {"Kommersialisering", "Marknadsstrategi", "Produktutveckling & testning", "Konceptutveckling", "Produktlansering", "Affärsanalys", "Bedömning"}, {"Kommersialisering", "Marknadsstrategi", "Produktutveckling & testning", "Konceptutveckling", "Produktlansering", "Affärsanalys", "Bedömning"})
in
    #"Expanded ActivityStatus"

Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

karhus01 · ‎01-05-2021

Thanks a lot @CNENFRNL !

This works perfectly, you saved my day and my headache 😁

Stay safe!

Power Query Nested JSON (List -> List of Records -> Record)

Helpful resources

Power BI Monthly Update - August 2025

Fabric Community Update - August 2025

How to Get Your Question Answered Quickly

Join us at FabCon Vienna from September 15-18, 2025

Power Query Nested JSON (List -&gt; List of Records -&gt; Record)

Helpful resources

Power BI Monthly Update - August 2025

Fabric Community Update - August 2025

How to Get Your Question Answered Quickly

Power Query Nested JSON (List -> List of Records -> Record)