Forum Discussion

BerlinAlexander's avatar
BerlinAlexander
Helper II
6 years ago
Solved

PowerBI Service doesn't read commas (as separators) correctly

I live in a country in which a comma (,) is normally used as a decimal separator. E.g. one and a half is 1,5.

 

I built a pipeline in PowerBi Desktop that imports data from a sharepoint folder and uses it to produce a dashboard. It works great, the only problem is that there's no way to automate the refresh.

 

So my idea was to build a dataflow to import exactly the same input data from the sharepoint folder and then schedule the refresh of the dataflow. It works ok. The problem is that the numeric data in the dashboard produced this way is completely wrong.

 

I spent several h trying to figure out why and now I see it: the measures are imported incorrectly. PowerBI Service seems not to read commas. So if I have "23,34" in my input file on the sharepoint, it's imported as "2334" into the dataflow. 

 

I've now tried everything possible to change that. I've tried importing the data as "type text" (to change the type later) but it doesn't help - the result is always "2334". I've also tried changing my language setting in the PowerBI Service into my regional language, instead of English, but it doesn't help either.

 

Any ideas what to do? The dataflow is useless to me if the numbers aren't correct.

 

Here's the code:

 

let
  Source = SharePoint.Files("https://rbcom.sharepoint.com/[my address]", [ApiVersion = 15]),
  #"Filtered rows" = Table.SelectRows(Source, each [Name] = "Local measures.csv"),
  #"Filtered hidden files" = Table.SelectRows(#"Filtered rows", each [Attributes]?[Hidden]? <> true),
  #"Invoke custom function" = Table.AddColumn(#"Filtered hidden files", "Transform file (2)", each #"Transform file (2)"([Content])),
  #"Renamed columns" = Table.RenameColumns(#"Invoke custom function", {{"Name", "Source.Name"}}),
  #"Removed other columns" = Table.SelectColumns(#"Renamed columns", {"Source.Name", "Transform file (2)"}),
  #"Expanded table column" = Table.ExpandTableColumn(#"Removed other columns", "Transform file (2)", Table.ColumnNames(#"Transform file (2)"(#"Sample file (2)"))),
  #"Changed column type" = Table.TransformColumnTypes(#"Expanded table column", {{"Source.Name", type text}, {"Branch", type text}, {"Product", type text}, {"Price", type number}})
in
  #"Changed column type"

 

Need Help
  • BerlinAlexander's avatar
    BerlinAlexander
    6 years ago

    I've now figured it out. I needed to change helper files.

     

    EDIT: It worked for one file and is not working for the rest.

2 Replies