Forum Discussion
Tables & Relationship: Value for empty IDs when no rows are present
Hi,
I have 2 tables - one Ad, the other Conversions. Each row on Conversions will have an associated row on the Ads table. Not all Ads will have an associated Conversions row.
Here's my starting point:
There are 3 Ad Groups from the Ads table. Total Impressions and Total Clicks come from the Ads table. Total Conversions comes from the Conversions table. You'll see that one of the Ad Groups doesn't have Conversions. This is the expected behaviour/layout.
When I add in the field Conversion ID from another table, this is the what happens, as expected:
The Ad Group that doesn't have Conversions disappears. This is now what I'm looking for. I want someone to be able to see Total Impressions across all Ad Groups, even if one doesn't have Conversions. Whereas this behavious reduces down the Total Impression metrics.
I'd like the Conversion ID value for the missing ID to show "No Conversions". But this doesn't appear as a row in the data.
How can I add this into my data to make this happen? Do I need to add a placeholder row into the Conversions table where ID = "No Conversions" and reference this in a formula?
I would really appreciate some help, I can't figure out how to do this.
Thanks,
Mark
In Power Query, in the Conversion Table you can add rows of Ad Group Names that appear in the Ad Table but not in the Conversion Table and then assign the 'No Conversions' to the Conversion Id. The existing relationships in your model should then bring in the 'No Conversions' when you use that column.
The function to add to your Conversion Table would beTable.Combine( { #"Changed Type", Table.FromList( List.RemoveMatchingItems( adTable[Ad Group Name], #"Changed Type"[Ad Group Name] ), Splitter.SplitByNothing(), type table [Ad Group Name = text] ) } )#"Changed Type' is the previous step in the Conversion Table
adTable[Ad Group Name] must be your Ad table name and the Ad Group Name column
This should give you the additional rows in your Conversion Table which you could then do a replace values on the null values in the Conversion Id column changing them to 'No Conversions.'
EDIT: Make sure to replace the null values in the conversion column with 0.
Hope this works for you.
10 Replies
jgeddesSuper User
Depending on how your model is structured you may be able to use a measure to get the conversion id instead of using the column.
Something likeConversion ID = IF( ISBLANK(LOOKUPVALUE(idTable[Conversion ID], idTable[Ad Group Name], SELECTEDVALUE(adTable[Ad Group Name]))), "No Conversions", LOOKUPVALUE(idTable[Conversion ID], idTable[Ad Group Name], SELECTEDVALUE(adTable[Ad Group Name])) )would work. The 'SELECTEDVALUE(adTable[Ad Group Name])' would need to be the column that is populating the rows of your visual. The idTable is the table where the conversion ids are stored.
Hope this gets you pointed in the right direction.