Creating a new column according to reference table

Question

​

I am relatively new to PowerBI and I was wondering if someone would be able to help with something I am stuck on. I have the following table (ProductDate) with information in the format below:

Product number	Date	Day of week
5JR38	16 September 2022	Friday
5JR38	17 September 2022	Saturday
5JR38	18 September 2022	Sunday
7QP13	12 September 2022	Monday
7QP13	13 September 2022	Tuesday
7QP13	14 September 2022	Wednesday
7QP13	15 September 2022	Thursday
7QP13	16 September 2022	Friday
7QP13	17 September 2022	Saturday
7QP13	18 September 2022	Sunday

​

I also have another table (ProductStock) with the number of stock available for a specific day of the week range:

Product number	Day ref	Stock
5JR38	FriO	20
5JR38	WEnd	65
7QP13	MFriO	7
7QP13	MidWeek	13
7QP13	WEnd	18

​

The Day ref column in the table above refers to a range of dates which are stored in a separate table (DayReference) as follows:

Day ref	Day of the week
FriO	Friday
MFriO	Monday
MFriO	Friday
WEnd	Saturday
WEnd	Sunday
MidWeek	Tuesday
MidWeek	Wednesday
MidWeek	Thursday

​

The 'Day ref' column in the ProductStock table does not overlap (i.e. for a 'Product Number' there is no 'Day ref' which would refer to a day of the week twice).

What I am trying to do is create a new column using DAX in the first table (ProductDate) with the 'Day ref' populated from the ProductStock tablewith their corresponding day of the week. The expected result should be something like this:

Product number	Date	Day of week	Relevant Day ref
5JR38	16 September 2022	Friday	FriO
5JR38	17 September 2022	Saturday	WEnd
5JR38	18 September 2022	Sunday	WEnd
7QP13	12 September 2022	Monday	MFriO
7QP13	13 September 2022	Tuesday	MidWeek
7QP13	14 September 2022	Wednesday	MidWeek
7QP13	15 September 2022	Thursday	MidWeek
7QP13	16 September 2022	Friday	MFriO
7QP13	17 September 2022	Saturday	WEnd
7QP13	18 September 2022	Sunday	WEnd

I don't really want to create a many-to-many relationship between those tables and would prefer if I add a new column as shown above. Any help or guidance will be much appreciated.

​

Thank you

Answer 1

You can achieve this in the Advanced Query Editor. Just open the Advance Editor for table ProductDate and try to incorporate this below code there-

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMvUKMrZQ0lEyNFMITi0oSc1NSi1SMDIwMgKKuRVlpiRWKsXqICkzx1QWnFhSWoSh0AKLwtI8mDLzwABDY5AyI0xlvvmYyowxlYWUphZjqDPBVBeempKHRaUpFhMzSoswFeIPGLgyQgEDV4gnYGIB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Product number" = _t, Date = _t, #"Day of week" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Product number", type text}, {"Date", type date}, {"Day of week", type text}}),
    
    //--------------------------
    //New steps started from here
    //---------------------------
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Day of week"}, DayReference, {"Day of the week"}, "DayReference", JoinKind.LeftOuter),
    #"Expanded DayReference" = Table.ExpandTableColumn(#"Merged Queries", "DayReference", {"Day ref"}, {"DayReference.Day ref"}),
    #"Merged Queries1" = Table.NestedJoin(#"Expanded DayReference", {"Product number", "DayReference.Day ref"}, ProductStock, {"Product number", "Day ref"}, "ProductStock", JoinKind.Inner),
    #"Expanded ProductStock" = Table.ExpandTableColumn(#"Merged Queries1", "ProductStock", {"Day ref"}, {"ProductStock.Day ref"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded ProductStock",{"DayReference.Day ref"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"ProductStock.Day ref", "Relevant Day ref"}}),
    #"Sorted Rows" = Table.Sort(#"Renamed Columns",{{"Product number", Order.Ascending}, {"Date", Order.Ascending}})
in
    #"Sorted Rows"

Here is the output-