cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Advocate II
Advocate II

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.

3 REPLIES 3
Highlighted
Super User II
Super User II

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

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

Thanks,

Nathan

Highlighted
Community Support
Community Support

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

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"

Capture9.JPG

Close&&apply, return to data model

Create relationships

Capture10.JPG

Capture11.JPG

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.

 

 

Highlighted
Advocate II
Advocate II

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

Hi @natelpeterson !

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

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors