Connect Month and Date in Merged Data

[tuncay]

Dear community,

I have merged two tables with each other. Both are showing data for several customer and material numbers.

One table is showing prices that are valid for a certain period of time and the other table is showing the quantitiy of material for each month. My aim is to combine both with each other.

It should be like that: Based on the SAP Validity it should show the price..

January Price: showing only the old price: 3466,7

February Price: 3466,7

There is no March, but it sould have been created..

April Price: 34448

[Anonymous]

Hi @tuncay ,

Following the logic in the code when [Month] = 1, [MonthYear] >= [SAP Validity Start] and [MonthYear] <= [SAP Validity End] result is false, it should output null.

Are there any conditions that need to be added or modified for this part of the logic?

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group

[Omid_Motamedise]

Pease provide sample data.

[tuncay]

Hello Philip,

I am using the following code. I try to connect the dates via MonthYear and I have the following problem: 

let
    Source = Table.NestedJoin(S4C_Report, {"Customer ID", "Material"}, SAP_Export, {"Customer ID", "Material"}, "SAP_Export", JoinKind.LeftOuter),
    #"Expanded SAP_Export" = Table.ExpandTableColumn(Source, "SAP_Export", {"SAP Price", "per", "SAP Validity Start", "SAP Validity End"}, {"SAP Price", "per", "SAP Validity Start", "SAP Validity End"}),
    #"Cleaned Text" = Table.TransformColumns(#"Expanded SAP_Export",{{"Key Figure", Text.Clean, type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Cleaned Text","Quantity Actuals2023","Quantity ACT 2023",Replacer.ReplaceText,{"Key Figure"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Replaced Value",{{"Month", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Quantity", each if [SAP Validity Start] >= #date(2024, 1, 1) then [Value] else null),
    #"Added MonthYear" = Table.AddColumn(#"Added Custom", "MonthYear", each Text.From([Month]) & "." & Text.From(Date.Year([SAP Validity Start]))),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added MonthYear",{{"MonthYear", type date}}),
    #"Added Price Column" = Table.AddColumn(#"Changed Type1", "Price", each if [MonthYear] >= [SAP Validity Start] and [MonthYear] <= [SAP Validity End] then [SAP Price] else null),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Price Column",{"Product Hierarchy", "Sold-to-party", "Customer ID", "Product Type", "Material", "Intarder Flag", "Key Figure", "Month", "MonthYear", "Value", "SAP Price", "per", "SAP Validity Start", "SAP Validity End", "Quantity", "Price"}),
    #"Removed Columns" = Table.RemoveColumns(#"Reordered Columns",{"Product Type", "per", "Value", "Key Figure"}),
    #"Sorted Rows" = Table.Sort(#"Removed Columns",{{"MonthYear", Order.Ascending}})
in
    #"Sorted Rows"

[PhilipTreacy]

Hi @tuncay 

What rules are you using to decide which price you want?

Please provide some sample data.  Cant work with images.

Also please provide an example of a row of data that you want as a result.

Regards

Phil