Power query - column name in source json are changin

[nirs]

hi,

first post here, wanted to thank for the chance to do it

im pretty new to power query but found it to do exactly what we need here - basically we are building dashboard for campaign stats where all the stats are recieved through the marketing platform rest API

what i tried to do is to build dynamic queries in power query that each time a user choose spesific campaign in the worksheet - it will query that spesific campaign stats from the API

the issue i encounter is that after i call the API and start expanding each column each step has unique id of his own and this ID`s also change between campaign, and when user change campaign i need the Table.ExpandRecordColumn to dynamic to exapnd the relevant ID`s to that campaign and im not able to achieve that

any idea how i can achieve this? (maybe there is better way to retrieve the JSON in first place?)

(JSON example and query code are below)

{
    "data": {
        "name": "something",
        "total_stats": {
            "revenue": 0,
            "conversions": 94,
            "conversions1": 197,
            "conversions_by_entry_time": 192,
            "conversions1_by_entry_time": 203,
            "entries": 1056
        },
        "step_stats": {
            "067a4e67-c919-4ddb-b15f-a2fc25078690": {
                "name": "something",
                "revenue": 0,
                "conversions": 6,
                "conversions1": 14,
                "conversions_by_entry_time": 9,
                "conversions1_by_entry_time": 14,
                "messages": {
                    "webhook": [
                        {
                            "sent": 412,
                            "unique_recipients": 412
                        }
                    ]
                }
            },
            "a7c49caf-ff07-46f2-a170-490f4bae2464": {
                "name": "something",
                "revenue": 0,
                "conversions": 0,
                "conversions1": 1,
                "conversions_by_entry_time": 0,
                "conversions1_by_entry_time": 1,
                "messages": {
                    "webhook": [
                        {
                            "unique_recipients": 1056,
                            "sent": 1056
                        }
                    ]
                }
            },
            "0829260f-cfa7-42f2-8f6d-2cdc5964561f": {
                "name": "something",
                "revenue": 0,
                "conversions": 6,
                "conversions1": 30,
                "conversions_by_entry_time": 14,
                "conversions1_by_entry_time": 30,
                "messages": {
                    "webhook": [
                        {
                            "sent": 644,
                            "unique_recipients": 644
                        }
                    ]
                }
            },
            "081cf255-20ab-40d6-a733-0010fa574722": {
                "name": "something",
                "revenue": 0,
                "conversions": 82,
                "conversions1": 152,
                "conversions_by_entry_time": 169,
                "conversions1_by_entry_time": 158,
                "messages": {
                    "email": [
                        {
                            "unique_recipients": 941,
                            "sent": 941,
                            "delivered": 937,
                            "bounces": 9,
                            "clicks": 40,
                            "unique_clicks": 31,
                            "reported_spam": 0,
                            "opens": 210,
                            "unique_opens": 146
                        }
                    ]
                }
            }
        }
    },
    "message": "success"
}

full code for query:

let
    Source = Excel.CurrentWorkbook(){[Name="Canvas_starting_datesTable"]}[Content],
    #"Invoked Custom Function" = Table.AddColumn(Source, "steps stats", each #"Dynamic Canvas steps details"([Relevant Canvas], [formated creation date], [formated current date])),
    #"Filtered Rows" = Table.SelectRows(#"Invoked Custom Function", each ([index row] = null)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Relevant Canvas", "created date", "Today"}),
    #"Expanded steps stats" = Table.ExpandRecordColumn(#"Removed Columns", "steps stats", {"data"}, {"steps stats.data"}),
    #"Expanded steps stats.data" = Table.ExpandRecordColumn(#"Expanded steps stats", "steps stats.data", {"name", "total_stats", "variant_stats", "step_stats"}, {"steps stats.data.name", "steps stats.data.total_stats", "steps stats.data.variant_stats", "steps stats.data.step_stats"})
in
    #"Expanded steps stats.data"

[v-sihou-msft · Accepted solution]

@nirs

According to your description, you already get data from API into Excel workbook. Now we want to dynamically select item then expand the corresponding records. Right? 

In this scenario, you can retrieve the list of all user campaigns first. Convert it into a list. 

Then you can create a query parameter with that list. 

Now you can use Query Parameter to filter rows, and then expand the corresponding records. Please refer to link below: 

POWER BI DESKTOP QUERY PARAMETERS, PART 1

Regards,

[nirs]

Many many thanks

always good to learn something new

[irisp]

When you are working with JSON, These tools will really help.

JSON formatter

JSON Validator