Import from CSV in Power BI | Fixing missing decimals and columns

How to Power BI2021-03-19

csv power bi#import csv power bi#power bi csv#power bi import csv#import csv in power bi#csv in power bi#missing decimals power bi#missing columns power bi#power bi missing decimals#csv file in power bi#power bi csv data source#importar csv power bi#power bi csv file#power bi csv decimal separator#power bi#power bi tutorials for beginners#power bi desktop

16K views|3 years ago

💫 Short Summary

The video discusses how to fix two common problems when importing data from CSV files into Power BI: the disappearance of decimals and the issue of columns not showing up. It demonstrates the solutions by adjusting regional settings and data types to ensure the correct interpretation of decimal separators, and by removing optional arguments to allow new columns to appear after data refresh.

✨ Highlights

📊 Transcript

✦

The video discusses two common problems when importing data from CSV files into Power BI: decimals disappearing and new columns not showing up.

00:00

Two CSV files with different decimal and thousand separators are shown.

The issue with decimals disappearing is due to regional settings, and the solution is to change the regional settings for the whole workbook.

To handle different decimal separators within the same Power BI file, the video demonstrates using a specific locale for the data type.

A dynamic approach to handling unknown decimal separators in CSV files is also presented using Power Query language.

✦

The video demonstrates how to change regional settings in Power BI to handle different decimal separators in CSV files.

03:02

Go to File > Options > Options

Select Regional Settings under Current file and choose a different region that uses the desired decimal separator.

The video also shows the use of a specific locale for data type assignment to handle different decimal separators within the same Power BI file.

✦

A new column is added to a CSV file, and the issue of it not showing up in Power BI is resolved by removing an optional argument in the source step.

06:01

The formula in the source step contains an optional argument that defines the number of columns.

By removing this optional argument, the new column appears after refreshing the data in Power BI.

✦

The video demonstrates how to fix data type and new column issues when combining data from CSV files in a folder in Power BI.

08:09

Fixing the data type issue by selecting the sales column and choosing to assign a data type with a specific locale.

Removing the optional argument 'columns equals two' from the formulas for transform file and sample file to ensure new columns flow into Power BI.

The video encourages viewers to share any other problems they have when connecting to CSV files and emphasizes subscribing for more content.

00:00 when i import data from a csv file into

00:02 power bi

00:03 i often encounter two problems the first

00:06 problem

00:07 is that sometimes my decimals disappear

00:10 and i have

00:10 way too big values in my result now the

00:14 second problem

00:15 that i sometimes have is that i add

00:17 columns to my data source file

00:18 csv file and then i refresh and they

00:21 don't show up in power bi

00:23 now let's see how we can fix both of

00:25[Applause]

00:28 them

00:33 welcome to how to power bi my name is

00:35 boss and if this is the very first time

00:37 for you visiting this channel then make

00:38 sure to hit that subscribe button

00:40 if you want to stay up to date on all of

00:42 my videos now let's have a look at the

00:45 csv files that we're going to import

00:47 into power bi

00:48 so here you have the data from two csv

00:50 files that we are going to import

00:52 into power bi and they're almost the

00:54 same the only difference is

00:56 the decimal separators and the thousand

00:58 separators so you see

00:59 in the first data set we have the dots

01:03 as a decimal separator and comma there's

01:05 a thousand separator

01:06 in the second data set we have it the

01:08 other way around okay

01:10 now let's go to power bi and import the

01:12 data from these two csv files

01:14 so go to get data and then text csv

01:17 choose the first one and then i leave

01:19 everything as this i'm just gonna click

01:21 on transform data

01:22 and then i do it one more time for the

01:24 other one so no source deck csv

01:27 and over here this is the other one and

01:29 also here leave everything after this

01:31 and just click on ok so now that we have

01:33 both data sets

01:34 inside of power bi let's compare them to

01:36 with each other so

01:38 for the first one we had commas as a

01:41 decimal separator

01:42 and you see everything seems to be

01:44 working fine so

01:45 we have first the source step then we

01:48 promoted the headers and then

01:52 you see over here we have commas now we

01:54 are going to change the data type

01:55 and boom it works now then for the other

01:59 one

02:00 we had dots as the decimal separator so

02:02 source tab

02:03 promoted the address and then now we're

02:06 going to change the data type

02:08 and it doesn't work now why did it work

02:11 for the first data set where the

02:13 commerce and not for the second one

02:15 well that has to do with your regional

02:17 settings so

02:18 by default power bi picks it up from the

02:21 regional settings

02:22 in windows you can just look for

02:25 regional

02:26 and format and then here you see for me

02:29 that's english europe now and in this

02:32 format

02:32 the decimal separator is comma and

02:34 therefore it applied to the right data

02:37 type

02:37 using the comma decimal separator but it

02:40 didn't work for that

02:41 second data set where the data as a

02:43 decimal separator so what can i do then

02:45 well we can also change the regional

02:47 settings for the whole workbook

02:48 to do that you can go to file and then

02:52 optional settings options and then you

02:55 go to

02:55 regional settings under current file and

02:58 then from here

02:59 we have a drop down and we can change

03:01 the regional settings

03:02 to a region which uses

03:06 the dot as a decimal separator and then

03:08 it would work for

03:10 the second data set but not for the

03:11 first one okay so let's give this a try

03:14 so i'm going to go and choose here

03:16 english uk

03:19 press on okay and now i'm going to go to

03:22 the data set where we have

03:23 the dots as a decimal separator i'm

03:26 going to delete

03:27 the change types up then i'm going to

03:30 redo that

03:31 and i'm gonna assign the data type

03:34 decimal number and you see now it works

03:37 however if i would have commas now the

03:39 other way around

03:40 well then it wouldn't work and what if

03:43 you have some queries where you have a

03:45 dot as a decimal separator

03:47 and other queries where there is the

03:49 comma as a decimal separator

03:51 then you have to go to your column and

03:54 then

03:54 assign a data type using a certain

03:57 locale

03:58 okay so using local and we need to

04:00 choose a local that uses the comma here

04:02 as a decimal separator for example

04:04 germany

04:07 click on ok you see it works now let's

04:09 have a look at the formula that got

04:11 generated for the last step now you see

04:13 it uses a function table the transform

04:15 column types

04:16 and basically it's the normal function

04:19 that's being used when you change the

04:21 data types

04:22 but there is an optional argument that

04:23 was added over here at the end

04:25 d e d e for germany okay so this is

04:28 basically the only thing that needs to

04:30 be added

04:31 to change the data type using a certain

04:33 lookout now we can make this

04:35 more dynamic if you don't know what the

04:36 decimal separators

04:38 are going to be for the csv files that

04:40 you're connecting to

04:41 you can do the following you can go one

04:43 step before changing the data types

04:45 and then you insert a new step and we

04:48 can

04:48 look into the sales column so over here

04:51 just type in

04:52 sales in between square brackets okay

04:55 and

04:56 we're going to have a look at the first

04:57 value that we find in this list

04:59 okay so for this we can use the curly

05:01 brackets

05:02 type in 0 to get the first value there

05:04 you go so now we can use a function

05:07 that's called

05:08 text dot position off

05:11 and we want to check the value and so

05:13 that's already there

05:14 and then we want to look for a comma

05:21 now we can duplicate this so just copy

05:24 it

05:25 and we can check if this is bigger than

05:29 and then we just paste it in there again

05:32 and replace the comma

05:34 with a dot now if this is true that must

05:37 mean that we have the comma

05:39 as a decimal separator so let's rename

05:41 that step

05:42 and i'm going to call this one comma

05:46 question mark then i go to the very last

05:48 step

05:49 okay now here we have to just update the

05:52 reference

05:54 to comma and instead we want to refer

05:57 to the promoted header step and now we

06:00 can go to that

06:01 very last argument and we can say if

06:05 there's a comma so i can just refer to

06:08 the

06:08 comma step then i want to have the

06:14 else there's appear as a dot as the

06:17 decimal separator

06:18 so we could use a locale like the uk

06:22 and if i then switch the data source

06:24 which currently is using

06:25 commas to something that's using

06:28 dots as a decimal separator then you see

06:31 for the common step

06:32 now this one returns fast and for the

06:35 very last step

06:36 it now applies the data type using the

06:39 uk format

06:40 okay now let's talk about the second

06:42 problem that i often have and that is

06:44 new columns that get added to the csv

06:48 files now here in our csv file i'm going

06:51 to add one extra column for the cost

06:53 and then let's save the file and go back

06:56 to power bi

06:57 i'm going to click on refresh i see

06:59 nothing happens

07:00 now what's going on is the following so

07:03 if you go back to the source step

07:05 and you have a closer look at the

07:06 formula you see there is one argument

07:08 that defines the number of columns

07:10 and actually this is a optional argument

07:13 we can take it out

07:15 so i'm just gonna take it out delete it

07:19 press enter and now you see the cost

07:22 column

07:23 shows up i'm gonna click on the very

07:24 last step and it's solved

07:27 so that was the easy fix and now you

07:29 might be wondering does all of this also

07:31 work

07:31 when you have csv files that are in the

07:33 folder and then you're going to connect

07:35 to that folder

07:36 and combine the data from all of the csv

07:38 files yes

07:39 it's more or less the same now let me

07:41 show you i'm going to

07:43 go here to new source again and then i

07:46 go to more

07:47 connect to a folder then click on okay

07:50 transform data

07:51 and then here we can click on combine

07:53 files in the content column header

07:56 and i leave everything so this just

07:58 click okay

07:59 now we have here the combined data sets

08:01 of two

08:02 csv files but you see the sales column

08:04 still has the wrong data type assigned

08:06 so we have to fix that so we can redo

08:08 the last step

08:09 and so just go one step back over here

08:12 select the sales column

08:14 and then choose to assign a data type

08:17 using a certain account

08:19 here we have a decimal number and then i

08:21 choose locale that has

08:24 comma as a decimal separator so in

08:27 germany

08:28 and click ok i see that fixes it

08:32 okay so that's it about the data types

08:34 what about the new columns

08:35 that are added to the csv files that are

08:38 in the

08:39 folder well for this we have to go to

08:41 the folder that's called transform file

08:43 from data

08:44 okay so let's go there and then you go

08:47 to the

08:48 function transform file and then let's

08:50 have a look at the formula

08:52 also here you see columns equals two

08:55 which you can just take out and then you

08:57 can also go to

08:58 transform sample file and then to the

09:01 source tab

09:02 and then also here just take out columns

09:05 equals

09:06 two and if you then have new columns and

09:09 you

09:10 refresh then you see that the cost

09:12 information

09:13 nicely flows into power bi and this is

09:15 how you can solve two common problems

09:17 that a lot of people have when they

09:19 connect to csv files

09:21 using power bi now if you have any other

09:24 problems then just

09:25 share them with me in the comment

09:26 section below and don't forget to

09:28 subscribe if you want to stay

09:30 up to date on all of my videos and i

09:32 hope to see you in the next video

💫 FAQs about This YouTube Video

1. How can the issue of disappearing decimals when importing CSV files into Power BI be fixed?

The issue of disappearing decimals when importing CSV files into Power BI can be fixed by adjusting the regional settings in Power BI to match the format of the CSV files. This ensures that the correct decimal and thousand separators are used, preventing the values from being interpreted incorrectly. Additionally, specific locales can be specified for data types to handle different decimal separators within the same Power BI file.

2. What can be done if columns added to a CSV file do not show up in Power BI after refreshing?

If columns added to a CSV file do not show up in Power BI after refreshing, the issue can be resolved by ensuring the number of columns argument is optional or by adjusting the settings in Power BI to accommodate the changes in the CSV file. By making the necessary adjustments, the new columns should appear correctly in Power BI.

3. How can the mismatch of decimal separators between CSV files and Power BI be addressed?

The mismatch of decimal separators between CSV files and Power BI can be addressed by carefully managing the regional settings and specifying the appropriate locales for data types. This ensures that the decimal separators are correctly interpreted, preventing errors and discrepancies in the data.

4. What is the solution for handling new columns that are not showing up in Power BI when connected to a folder of CSV files?

The solution for handling new columns that are not showing up in Power BI when connected to a folder of CSV files involves ensuring that the column argument is optional and making adjustments in Power BI to properly incorporate the new columns. By following these steps, the new columns should be successfully integrated into Power BI.

🎥 Related Videos