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.
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 be
Table.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.
jgeddes ,
Just one last little thing. It's pretty much working, but I'm now seeing a blank rows for each Ad Group in the table:
I would like to only show a blank row if there are no Conversion IDs, highlighted in yellow. I think it's showing a blank rows as there could be days where there are no conversions?
What do you think?
Mark
Thanks,
Mark
- jgeddes2 years ago
Super User
Those rows are showing because the measures for impressions and clicks have values for them. You can edit the impressions and clicks measures to show only when there is also conversions. Or filter them out with the filter pane.