Forum Discussion
Tables & Relationship: Value for empty IDs when no rows are present
- 2 years ago
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.
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 like
Conversion 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.
- Anonymous2 years agoNot applicable
HI jgeddes ,
There could be multiple conversion IDs for an Ad Group. It looks like this only works if there's only one conversion ID per Ad Group?
- jgeddes2 years ago
Super User
You are correct. LOOKUPVALUE will only work with unique values. That is my oversight.
What table are the conversion IDs in?- Anonymous2 years agoNot applicable
Conversion ID is in the Conversion Table. Everything else should be in the Ads table.
Thanks.