Forum Discussion

Asantos2020's avatar
Asantos2020
Icon for Advocate II rankAdvocate II
6 years ago

Table combining ID Columns from 02 Tables + other columns' data

Hello folks,

I have the following scenario:

Have sales goal monitored through a Table Visual, showing:

SalesManGoalSales

Leon

   Customer1

   Customer2

   Customer3

 

1000

500

800

 

100

200

600

Mark

   Customer5

   Customer9

   Customer10

 

1000

500

800

 

100

200

600

 

I have the following tables:

SalesGoalTable

DateCustomerIDCustomerNameActivitySalesGoalSalesMan
10/01/2019123456John C.Retail100Mark
10/01/2019645123Mr. BeginnerRetail200Leon
10/01/2019789456Mr. CantFigureWholesale500Leon
10/01/2019978456Ms. PrettyRetail500Mark

 

SalesTable

DateCustomerIDCustomerNameActivitySalesSalesMan
10/05/2019123456John C.Retail120Mark
10/09/2019005689Mr. FalkerRetail50Mark
10/10/2019789456Mr. CantFigureWholesale200Leon
10/25/2019963852Mr. EfraimRetail700Leon

 

The report is filtered by Date (from a calendar table created) and by Activity - also independently created.

 

I have appended these two tables, so that I can have a list of the customers who bought and the ones who had goals set, but didn't show up in the Sales Fact Table, but when putting them on a table visual, I get Grand Total Sales and the SalesGoal Column contains no value.

 

Hope it's enough information to get a light towards the solution.

 

Thanks a lot for any help.

Need Help

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Asantos2020  - Could you share a mock-up of the result you're looking for? 

    Thanks,

    Nathan

    • Asantos2020's avatar
      Asantos2020
      Icon for Advocate II rankAdvocate II
      6 years ago

      Hi Anonymous !

      I'll check the answer below to see if this solves the problem. If not, I'll be more than happy to provide more info.

      Thank you for your time.

       

      Cheers,

      Antonio

  • v-juanli-msft's avatar
    v-juanli-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi Asantos2020 

    In Edit queries, 

    in "goal table", add a column by merging [SalesMan], [CustomerID], [Activity], [Date] columns.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTQN9Q3MjC0VNJRMjQyNjE1AzK88jPyFJz1gKyg1JLEzByQnIEBkPRNLMpWitVB1WZmYgrUCZIt0lNwSk3PzMtLLULWawTW65Oan4eh19zCEmIlSK9zYl6JW2Z6aVEqUCA8Iz8ntTgxB8Q2xW2ApbkF1IBiPYWAotSSkkpkq02RnB0LAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, CustomerID = _t, CustomerName = _t, Activity = _t, SalesGoal = _t, SalesMan = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"CustomerID", Int64.Type}, {"CustomerName", type text}, {"Activity", type text}, {"SalesGoal", Int64.Type}, {"SalesMan", type text}}),
    #"Inserted Merged Column" = Table.AddColumn(#"Changed Type", "link1", each Text.Combine({[SalesMan], Text.From([CustomerID], "en-US"), [Activity], Text.From([Date], "en-US")}, "__"), type text)
in
    #"Inserted Merged Column"

     

    in "fact table", add a date column which shows start date per month,

    then merge columns [SalesMan], [CustomerID], [Activity], [Start of Month]

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTQN9U3MjC0VNJRMjQyNjE1AzK88jPyFJz1gKyg1JLEzBywnAGQ9E0sylaK1QFrs4RpMzWzAFG+RXoKbok52alFyPpM0bUBEVSfuYUlxDqQTufEvBK3zPTSolSgQHhGfk5qcWIOiG1kADLBJzU/D2aCEdzBlmbGFqZGUBNc04oSM3OR7TZH0hoLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, CustomerID = _t, CustomerName = _t, Activity = _t, Sales = _t, SalesMan = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"CustomerID", Int64.Type}, {"CustomerName", type text}, {"Activity", type text}, {"Sales", Int64.Type}, {"SalesMan", type text}}),
    #"Inserted Start of Month" = Table.AddColumn(#"Changed Type", "Start of Month", each Date.StartOfMonth([Date]), type date),
    #"Inserted Merged Column" = Table.AddColumn(#"Inserted Start of Month", "link2", each Text.Combine({[SalesMan], Text.From([CustomerID], "en-US"), [Activity], Text.From([Start of Month], "en-US")}, "__"), type text)
in
    #"Inserted Merged Column"

    Close&&apply, return to data model

    Create relationships

    The data in this example is not completed, so you see blank rows above.

     

    Best Regards
    Maggie
    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.