PowerBI not recognizing text data imported from PostgreSQL when creating visual graphs

[VBeaklini]

Hello i´m doing a free online course on Power BI, managing a very low amount of data in exercises to learn how to use the program and all, and the class just introduced PostgreSQL to us, teaching how to import data from it and make some basic visual graphs.

However for some reason it is not working for me and i´m running into an error i do not understand, as i build a basic bars table of Sales x Product Category, the product category section on the x axis is blank, even though the data has been correctly imported from PostgreSQl (i can see the tables properly on the different tab). But if i try to do the same graph with Sales x Date, it works properly. I guess PowerBI is only reading correctly the data from the table when it comes to numbers, but not text?

Thanks for the help.

[VBeaklini]

I replied to my own post above instead of yours, posting this message here just in case you didn´t see it. Thanks in advanceif you can help.

[hnguy71]

Hi @VBeaklini ,

With your sample data there's only 1 category. The rest of the product IDs are missing categories. How are you expecting 4? Can you copy and paste a larger sample data? If you can share the pbix via OneDrive public link that would be nice to see your actual model.

[VBeaklini]

There are four categories (Eletrodomésticos, Eletroportáteis, Eletrônicos and Celulares), they are just not in the first 10 rows. I don´t know if it´s the language barrier or the fact that the copy and paste truncated the title of the columns and it made it a bit confusing, but it should be something more like this

ID :  SKU-XXXXXXXX

Nome : Geladeira Duplex

Categoria : Eletrodoméstico

Segmento : Doméstico

Marca: Brastemp

I dunno how to set a one-drive thing, public or not. Maybe i can send you an email with the csv files?

[hnguy71]

Hi @VBeaklini you can send me a personal message via the forums and I can supply you my email address. Alternatively, you can create a free onedrive account located here:

 https://onedrive.live.com/

Once you create the account, you can drag-drop your sample csvs or pbix and click the share button:

Then click copy link button to share the link with the community:

[VBeaklini]

Let´s see if i´ve done this right, these are the links for each file:

https://1drv.ms/u/s!AkA0_hLZOtc1gR9jh85cloGap8IR?e=SLzfGS

https://1drv.ms/u/s!AkA0_hLZOtc1gR5kHqtQyu2VtsxZ?e=bVW4gN

[hnguy71]

Hi @VBeaklini ,

The link works. Fantastic work. 

I think your issue is that your data needs to be transformed to be usable and is possible why you're only getting one column. I've attached the model for you to view based on the information provided. Let me know if this is what you're looking for.

[VBeaklini]

Okay, when you do it it works, but let me remind you that for this exercise we are supposed to upload those csv files to a data base in PostgreSQL, and then import it to PowerBI to play with it by making some graphs.

My mistake i should´ve sent the PowerBi file with the error in it in the first place, yeah i guess i have to transform the data somewhat but the class i´m on is automated and they´re not instructing us to fix anything, the data should´ve worked with no tempering required. And i don´t know what to transform in the data to make it work. Here´s the pbx file see if you can find what is wrong with it:

https://1drv.ms/u/s!AkA0_hLZOtc1gSJ6Yb6OOWn4QfvA?e=VJw5TZ

Thanks for the help.

[hnguy71 · Accepted solution]

Hi @VBeaklini ,

In your file, there's an issue with the relationships. You're keys aren't lining up to give you the right results:

They're going to return blank because the keys don't match. Try adjusting it and see if that works.

[VBeaklini]

Okay i know what is wrong, the data they sent us is all codified wrong. Observe that in TB_PRODUTO the Produto column are all written as SKU-XXXX, while in TB_VENDAS they are codified as SPXXXX. That is supposed to be the product id, they have to be the same in all tables. And in TB_VENDAS, the column TB_VENDEDOR (vendedor means salesman) has the same  sku code that is used for the products... so it is all messed up. No wonder i can´t do anyting. Thanks for the help.

[VBeaklini]

I don´t know how to attach a file to a post in this forums, and even though the data i´m working on is just a sample it´s still about 400+ rows to post it here, but i´m gonna try the first 10 lines so you can have an idea. Here is the Sales table:

ID-Produto	ID-Vendedor	Loja	Data Venda	ValorVenda
SKU-0000001	1009	SP8821	########	679
SKU-0000002	1006	SP8821	1/1/2012	832
SKU-0000003	1006	SP8821	2/2/2012	790
SKU-0000004	1003	SP8821	3/3/2012	765.32
SKU-0000005	1004	SP8821	4/4/2012	459.89
SKU-0000006	1005	SP8821	4/5/2012	590.98
SKU-0000007	1006	SP8821	4/6/2012	1000.91
SKU-0000008	1007	A9990	4/7/2012	1229
SKU-0000009	1003	SP8821	4/8/2012	1300

The data is in brazillian portuguese. The first column is the id of the product, the second is the id of the salesman, the third is the store, the fourth is the date of the sale (day/month/year), and finally the last one, the one that matters for the exercise, is the monetary value of the sale. The next table is for the product:

ID NOME CATEGORIA SEGMENTO MARCA
SKU-0000002 Geladeira Duplex Eletrodomésticos Doméstico Brastemp
SKU-0000007 Geladeira Duplex Eletrodomésticos Doméstico Brastemp
SKU-0000009 Geladeira Duplex Eletrodomésticos Doméstico Brastemp
SKU-0000011 Geladeira Duplex Eletrodomésticos Doméstico Brastemp
SKU-0000012 Geladeira Duplex Eletrodomésticos Doméstico Brastemp
SKU-0000013 Geladeira Duplex Eletrodomésticos Doméstico Brastemp
SKU-0000014 Geladeira Duplex Eletrodomésticos Doméstico Brastemp
SKU-0000015 Geladeira Duplex Eletrodomésticos Doméstico Brastemp
SKU-0000028 Geladeira Duplex Eletrodomésticos Doméstico Brastemp

From left to right we have id, name, category, segment and brand. In the category column, for the first 10 rows we have only one type, Eletrodoméstico (home appliance), but for the entire set of data we also have small appliances, cellphones and eletronics.

Now here is where my problem lies: when i create a simple graph, a bar graph, placing ValorVenda (sales value) on the y axis and Categoria (category) on the x axis, i only get one bar for the categories when i should be getting 4. And the nameplate for the category is blank. I also get this error when i try to create the same graph with any other part of the data that isn´t a number (date or monetary). I never had this problem with Power BI, i´ve only run into it in this particular class i suspect because we are required to load the data first to PostgreSQL and then import it to Power BI to simulate we are managing a data base. So i think the issue might have something to do with PostgreSQL. Any thoughts?

[hnguy71]

Hi @VBeaklini ,

Since you're just doing a free online course, the data must be sample data. Can you post the sample pbix? Maybe if I take a look I can likely see what is the cause of your issue.

Without seeing it, I'm guessing your Sales is not a numeric type, or perhaps you should create a measure to aggregate your sales amount.