Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
OPS-MLTSD
Post Patron
Post Patron

Help with DAX code filling in blank columns using data from Microsoft Access table

Hello,

 

I am trying to create a dashboard in Power BI using Microsoft Access Data, I have already converted the data from Access into Excel and have imported the data into Power BI query editor. While checking the data in the query editor, I noticed blank spaces for at least 3 columns (I have 14 columns in total). The 3 columns with missing data are: "Postal Code", "City", and "Address"

(More info on my project: I am trying to build a map of different workplaces in different cities. In my dashboard, I am planning on having a table with column names: "Workplace Name", "Address", "City", and "Postal Code" so that when a person choses a postal code, it shows where that workplace is)

As I mentioned, while looking through my data, I noticed that there are blanks in 3 of the columns mentioned above, I was wondering, if there is a DAX code that exists that can help me fill up the blanks for the address, postal code, and city, based on data available on Microsoft Access. For example, here is a sample of my Excel Sheet:

 

Workplace Name                                   Address                                Postal Code                       City

Lashore Gold Organization                   124 Sesame St                      L4M 2C1                            Acton

Executer Prints                                       (blank)                                  L2C 5B6                            Burlington

Panasoki View                                       36 Gervais Rd                        (blank)                               Timmins

Mining Way                                           14 Grey Blvd                         K0P 6O9                             Sudbury

Raps Inc                                                 20 Jane Dr                            N0P 1C4                             (blank)

Spark Int                                                104 Spark St                         (blank)                                Perth

Inner Circle                                            22 University Rd                   N8O Y7O                            (blank)

Dixie Central Org                                  (blank)                                   N5R 8D6                           Thunder Bay

 

As you can see, there are some blank spaces in the "Address", "Postal Code", and "City" columns, I was wondering, how can I fill up those blanks with the correct address, postal code or city info? I have a table in Access called "Employers" which contains the workplace names and their address, postal code, and city. I would like to know how I can use DAX or write a code to pull those information from the Access table to populate the blank field in Power BI. If you could help me out with this, I would really appreciate it!

 

thank you guys

2 REPLIES 2
Nathaniel_C
Super User
Super User

Hi @OPS-MLTSD ,
Go to Home tab, select Get Data, choose Access database and make the connection. This cuts out the Excel "middleman" and will hopefully be more successful.
Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

acc1.PNG

 

acc.PNG

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Hello, even when I get the data directly from Access, the blanks are still there. I would like to know, how can I fill up those blanks? 

In my case, I have several tables in my Access Database, I have combined 4 of those tables into one, and I am using information from that combined table that I created to create a dashboard on Power BI. But my problem is, there are several blank spaces in that Access table (please see above example for reference). My question, how can I fill up those blanks using information from other tables in Access. Unfortunately, for soem reason when I joined the tables together in Access, some rows still had blanks (I have checked with sevral sources, and the joining process was correct, so I knwo the problem isn't there). 

 

Please let me know how I can fill up the blanks in Power BI.

 

thank you 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors