cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
New Member

Power query - column name in source json are changin

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)

Capture1.PNG

{
    "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"
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Microsoft
Microsoft

Re: Power query - column name in source json are changin

@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. 

 

33.PNG

 

Then you can create a query parameter with that list. 

 

77.PNG

 

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,

View solution in original post

3 REPLIES 3
Highlighted
Microsoft
Microsoft

Re: Power query - column name in source json are changin

@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. 

 

33.PNG

 

Then you can create a query parameter with that list. 

 

77.PNG

 

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,

View solution in original post

Highlighted
New Member

Re: Power query - column name in source json are changin

Many many thanks

 

always good to learn something new

Highlighted
New Member

workingRe: Power query - column name in source json are changin

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

 

JSON formatter

JSON Validator

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors