Forum Discussion

tuncay's avatar
tuncay
Helper III
1 year ago

Connect Month and Date in Merged Data

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

 

Need Help

4 Replies

  • PhilipTreacy's avatar
    PhilipTreacy
    Super User
    1 year ago

    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

     

  • tuncay's avatar
    tuncay
    Helper III
    1 year ago

    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"

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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