Forum Discussion
Compare difference between two tables.
The proper way would be to create a data model with Name and Medium dimension tables that would then control the two fact tables. But a simpler option would be to take advantage of the merging functions in Power Query that allow you to link tables by more than one column.
Pre Contents table:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcirKTMxT0lFyzU3MzAHSnnlKsToIYV+IqH9pCYpwsG8wptqAjPy8VKhwLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Medium = _t, Opted = _t]) in SourceContents table:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcirKTMxT0lFyzU3MzAHS/qUlSrE6CHFf7MLBvsFA0jMPRTAgIz8vFaY4FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Medium = _t, Opted = _t]), #"Merged Queries" = Table.NestedJoin(Source, {"Name", "Medium"}, #"Pre Consents", {"Name", "Medium"}, "Pre Consents", JoinKind.LeftOuter), #"Expanded Pre Consents" = Table.ExpandTableColumn(#"Merged Queries", "Pre Consents", {"Opted"}, {"Opted.1"}), #"Renamed Columns" = Table.RenameColumns(#"Expanded Pre Consents",{{"Opted.1", "Pre"}}), #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Concern", each if [Pre]="In" and [Opted] = "Out" then 1 else 0), #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Concern] = 1)) in #"Filtered Rows"
The proper way would be to create a data model with Name and Medium dimension tables that would then control the two fact tables. But a simpler option would be to take advantage of the merging functions in Power Query that allow you to link tables by more than one column.
Pre Contents table:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcirKTMxT0lFyzU3MzAHSnnlKsToIYV+IqH9pCYpwsG8wptqAjPy8VKhwLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Medium = _t, Opted = _t])
in
Source
Contents table:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcirKTMxT0lFyzU3MzAHS/qUlSrE6CHFf7MLBvsFA0jMPRTAgIz8vFaY4FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Medium = _t, Opted = _t]),
#"Merged Queries" = Table.NestedJoin(Source, {"Name", "Medium"}, #"Pre Consents", {"Name", "Medium"}, "Pre Consents", JoinKind.LeftOuter),
#"Expanded Pre Consents" = Table.ExpandTableColumn(#"Merged Queries", "Pre Consents", {"Opted"}, {"Opted.1"}),
#"Renamed Columns" = Table.RenameColumns(#"Expanded Pre Consents",{{"Opted.1", "Pre"}}),
#"Added Custom" = Table.AddColumn(#"Renamed Columns", "Concern", each if [Pre]="In" and [Opted] = "Out" then 1 else 0),
#"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Concern] = 1))
in
#"Filtered Rows"
Hi Stumbling along here so decided to post the tables to see if that makes things simpler to figure out.
Below is my "Pre-Consent" table
Below is the "Consents" table - these are realted by "Given By"
Thinking about it i would like to "Append" new Consents given to the 'Pre Consents' table in new columns. These would be Given By, Medium, Opted. When i do this it just adds new rows and i get a multiplcation of information. What i want is to add new Columns where the name matches and medium matches with new Opted values.
So the "Pre-Consents" table with be added with the new columns from 'Consents' Table where it pulls in updated preferences.
The above should allow me to report on where someone was Opted IN to a Medium but now Opted OUT.
Many Thanks