'How to join these tables with columns that don't always match?

I have 2 tables: google_analytics and marketing_costs. How can I join these 2 columns (google_analytics['source'] with marketing['medium']) even tho they don't always match?

Data Model join example

sqldatabase-designpowerquerydata-modeling


Solution 1:[1]

I think you're asking for a shared dimension table, then add a relation

let
    Sources = List.Combine({ google_analytics[source],  marketing_costs[platform] }),
    Source = List.Distinct( List.Sort( Sources )),

    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(),
        type table[Name = text], null, ExtraValues.Error),

    #"Added Index" = Table.AddIndexColumn(#"Converted to Table", "Source Id", 0, 1, Int64.Type)    
in
    #"Added Index"

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 ninMonkey

Related Questions