Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

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 star...
  • jgeddes's avatar
    jgeddes
    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 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.