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

[Asantos2020]

Hello folks,

I have the following scenario:

Have sales goal monitored through a Table Visual, showing:

SalesMan	Goal	Sales
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

Date	CustomerID	CustomerName	Activity	SalesGoal	SalesMan
10/01/2019	123456	John C.	Retail	100	Mark
10/01/2019	645123	Mr. Beginner	Retail	200	Leon
10/01/2019	789456	Mr. CantFigure	Wholesale	500	Leon
10/01/2019	978456	Ms. Pretty	Retail	500	Mark

SalesTable

Date	CustomerID	CustomerName	Activity	Sales	SalesMan
10/05/2019	123456	John C.	Retail	120	Mark
10/09/2019	005689	Mr. Falker	Retail	50	Mark
10/10/2019	789456	Mr. CantFigure	Wholesale	200	Leon
10/25/2019	963852	Mr. Efraim	Retail	700	Leon

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.

[v-juanli-msft]

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.

[Anonymous]

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

Thanks,

Nathan

[Asantos2020]

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