CODEWITHSUNDEEP
powerbipowerquery
variable
variable

Reputation: 9714

How to add an unknown dimension record using power query?

I have a flat table which I am modelling into a star schema.

Example: salesdate, productname, salesamount, agentname

I want to make Sales as fact table. Product and Agent as dimension tables.

Step 1: get data from dafasource. This gives me a query with salesdate, productname, salesamount, agentname

Step 2: duplicate the above query and name new query as Agents.

Step 3: in the query from step 2, remove the salesdate, productname, salesamount. Remove duplicate agentname. Add index stating with 1.

Step 4: merge query (left join) from step 1 with Agent on agentname. Remove agent name from query 1. Expand merged query so we now have agentid in fact table.

Problem: there are couple of sales for whom agentname is unknown. So they are appearing as null agentid in fact table. How can I add an unknown record in agent dimension for example id 1 as Unknown Agent. This is so that I can map the numm agentid to unknown record.

Upvotes: 0

Views: 575

Answers (1)

Aleksei Zhigulin
Aleksei Zhigulin

Reputation: 1634

You may add this code to Agents query:

Final = YourLastStep & #table({"agentname","agentid"},{{"Unknown Agent", 1}})

Upvotes: 1

Related Questions