Forum Discussion

Fregglino's avatar
Fregglino
New Member
1 year ago

Contents from different tables in one column

Hi, I have the following problem and have been trying to solve it for a long time.
I hope you can help me.
I have 2 tables in my Power BI project (simplified):
Table 1 (sales date, product, costs):
DateProductCosts
25.03.2022Name 1523
26.08.2022Name 2471
21.5.2023Name 1362

22.6.2023

Name 3215

5.1.2024

Name 2853

2.8.2024

Name 3235

21.1.2025

Name 2856

 

 

Table 2 (year, product, sales target):

 

YearProductSales target for year
2022Name 11000
2022Name 22000
2022Name 31500
2023Name 11000
2023Name 22000
2023Name 31500
2024Name 11000
2024Name 22000
2024Name 31500
2025Name 11000
2025Name 22000
2025Name 31500

 

I would like to create the following visual:

 

YearProduct

Total and objective mixed

2022Name 1Total cost of product 1
2022Name 2Sales Target from Table 2 (Copy)
2022Name 3Sales Target from Table 2 (Copy)
2023Name 1Total cost of product 1
2023Name 2Sales Target from Table 2 (Copy)
2023Name 3Sales Target from Table 2 (Copy)
2024Name 1Total cost of product 1
2024Name 2Sales Target from Table 2 (Copy)
2024Name 3Sales Target from Table 2 (Copy)
2025Name 1Total cost of product 1
2025Name 2Sales Target from Table 2 (Copy)
2025Name 3Sales Target from Table 2 (Copy)

 

 

Need Help

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi Fregglino ,
    You can use power query to ahcieve this, Click Transform Data and open power query

    Create a blank query and open advanced editor

    Paste the following code

     

    let
    Source1 = #"Table 1",
    Source2 = #"Table 2",
    #"Extracted Year" = Table.AddColumn(Source1, "Year", each Date.Year([Date]), Int64.Type),
    #"Grouped Rows" = Table.Group(#"Extracted Year", {"Year", "Product"}, {{"Total Costs", each List.Sum([Costs]), type number}}),
    #"Merged Queries" = Table.NestedJoin(Source2, {"Year", "Product"}, #"Grouped Rows", {"Year", "Product"}, "Grouped Rows", JoinKind.LeftOuter),
    #"Expanded Grouped Rows" = Table.ExpandTableColumn(#"Merged Queries", "Grouped Rows", {"Total Costs"}, {"Total Costs"}),
    #"Replaced Sales Target" = Table.ReplaceValue(#"Expanded Grouped Rows", each [#"Sales target for year"], each if [Product] = "Name 1" then [Total Costs] else [#"Sales target for year"], Replacer.ReplaceValue, {"Sales target for year"}),
    #"Renamed Columns" = Table.RenameColumns(#"Replaced Sales Target", {{"Sales target for year", "Total and objective mixed"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Total Costs"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"Total and objective mixed", type number}})
in
    #"Changed Type"

    Final output

     

    Best regards,
    Albert He

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