Wonky Conditional Columns Needed--Help?

[rsderby68]

Hello All,

I have a wonky situation I could use advice on the best solution. 

I run a Power BI report for a Vimeo OTT site.  I get data from their API, which includes a customer table (showing all our customers) and an event table (showing all platform events) which are relationship linked by the customer_id field.  My goal is to add columns to the customer table pulling in the latest event_date (to show when they last logged in) and latest ip_number (to get demographics data) for each corresponding customer_id in both tables. 

Details

Table: Vimeo_Platform_Events

Table includes all platform events for customer_id’s including each time they sign_in (a row for each time and dates for each time). 

Fields I need data from:

Vimeo_Platform_Event_Date

Vimeo_Customer_ID

Vimeo_Platform_Event

Vimeo_Customer_IP

Data I want:  for each Vimeo_Customer_ID where Vimeo_Platofrm_Event = “sign_in_complete” I want to get the LATEST (closest to whatever today is) Vimeo_Platform_Event_Date and corresponding Vimeo_Customer_IP and then add that data to new columns I create in the Vimeo_Customers table.

Table: Vimeo_Customers

I want to add these new columns to the Vimeo_Customers table:

Latest_Sign_In_Date = latest (from Platform table) Vimeo_Platform_Event_Date where Vimeo_Platform_Event = “sign_in_complete” for the matched Vimeo_Customer_ID in both tables. 

Latest_Customer_IP = corresponding (from Platform table) Vimeo_Customer_IP where latest  Vimeo_Platform_Event_Date where Vimeo_Platform_Event = “sign_in_complete” for the matched Vimeo_Customer_ID in both tables. 

Any suggestions how to get this?  Thanks for the help!   

[v-yanjiang-msft · Accepted solution]

Hi @rsderby68 ,

According to your description, here's my solution.

Sample:

Add a step in Vimeo_Customers table:

#"Merged Queries" = let 
table=Table.SelectRows(Vimeo_Platform_Events, each [Vimeo_Platform_Event]="sign_in_complete"and [Vimeo_Platform_Event_Date]=List.Max(Table.SelectRows(Vimeo_Platform_Events,(x)=>x[Vimeo_Customer_ID]=[Vimeo_Customer_ID]and x[Vimeo_Platform_Event]="sign_in_complete")[Vimeo_Platform_Event_Date])
in
Table.NestedJoin(#"Changed Type", {"Vimeo_Customer_ID"}, table, {"Vimeo_Customer_ID"}, "Vimeo_Platform_Events", JoinKind.LeftOuter),

Then expand the new column and select Vimeo_Platform_Event_Date and Vimeo_Customer_IP columns in the dialog.

Get correct result in Vimeo_Customers table:

Here's the whole M syntax:

Vimeo_Platform_Events table:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lY/BCsJADET/Zc8l7cxusu1feC+lBy2loKug/48RFK2C4m1IeMybvg9syBqoyVCFaGaI0dN5mcu4lHF7PJz202Xyk5kwqgAUaBeG6g5bjbxiN1PZLWX2hEwhVYgkGldI+lWHJgvaJF6Z0xN1Vai/kzJ3pvk/18fQF/rd1uQmy4993wvbVmA+lJ0wWRiGKw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Vimeo_Platform_Event_Date = _t, Vimeo_Customer_ID = _t, Vimeo_Platform_Event = _t, Vimeo_Customer_IP = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Vimeo_Platform_Event_Date", type date}, {"Vimeo_Customer_ID", Int64.Type}, {"Vimeo_Platform_Event", type text}, {"Vimeo_Customer_IP", type text}})
in
    #"Changed Type"

Vimeo_Customers table:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjYzMzM0NlaK1YlWMjE1Mrc0MzVXio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Vimeo_Customer_ID = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Vimeo_Customer_ID", Int64.Type}}),
    #"Merged Queries" = let table=Table.SelectRows(Vimeo_Platform_Events, each [Vimeo_Platform_Event]="sign_in_complete"and [Vimeo_Platform_Event_Date]=List.Max(Table.SelectRows(Vimeo_Platform_Events,(x)=>x[Vimeo_Customer_ID]=[Vimeo_Customer_ID]and x[Vimeo_Platform_Event]="sign_in_complete")[Vimeo_Platform_Event_Date]))
in
Table.NestedJoin(#"Changed Type", {"Vimeo_Customer_ID"}, table, {"Vimeo_Customer_ID"}, "Vimeo_Platform_Events", JoinKind.LeftOuter),
    #"Expanded Vimeo_Platform_Events" = Table.ExpandTableColumn(#"Merged Queries", "Vimeo_Platform_Events", {"Vimeo_Platform_Event_Date", "Vimeo_Customer_IP"}, {"Vimeo_Platform_Events.Vimeo_Platform_Event_Date", "Vimeo_Platform_Events.Vimeo_Customer_IP"})
in
    #"Expanded Vimeo_Platform_Events"

I also attach my sample below for your reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.