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
LuteceFalco
Helper II
Helper II

Value for mixed data

Hi all,

 

In one of our column, we have mixed data with numbers and text, example: AH34839, and in the same column we have only rows with numbers, exemple: 41774230 so when we try to import this Excel file to PowerBi, it's give us an error for this column, because of those mixed rows.

Is there any solution to resole this issue, to be accepted without errors in PBI .. so when we try to change the value, there si no porposition for "text and numbers" to chose.

 

Thanks a lot to all of you.

1 ACCEPTED SOLUTION

I found how to make that it's working. Maybe it's the same thing like you wrote me, but I'm glad that it's working well for me.

 

In Advanced Editor, "{{"Master Customer Name (Inc)", type text}" I replace "text type" by "general", same option for data type that you can find in Excel, but not in PBI.

So, when I pressed save, it's give me an error code, but at this moment I press on remove "changed type", and this is what's changing my data type in ANY, what I need actualy, it's same as general in Excel. So, I tested it, and it's show me now in my table all informations, even in filter, when I press now "Load More" it's show me all what I need.

 

Thanks a lot @Anonymous

 

View solution in original post

12 REPLIES 12
Anonymous
Not applicable

Hi @LuteceFalco

 

By creating a query, PBI Desktop creates a step called "changed value", in which Power BI tries to define the type of data you used, based on the first rows.

If the first rows are number, then type will be decimal or whole number.

So, if the next rows contains have text, the query will fail.

 

Turn off this step and define the data type yourself.

 

 

Hi @Anonymous,

 

I already tried this, but when I go to filter, and press "load more" in filters, it's still give me this error "[DataFormat.Error] We couldn't convert to Number"

 

 

Anonymous
Not applicable

could you please paste your code and a pic of the error?

This is the first time for me, so I hope that I give you what you need as information.

 

= Table.TransformColumnTypes(#"Reordered Columns",{{"Master Customer Number (Inc)", type text}})

 

01.JPG

It's refuse to load the rows with mixed data inside.

 

Anonymous
Not applicable

Can you please send me the query code?

Click on "Advanced Editor" and copy & paste the code.

let
Source = #"Report 1",
#"Added Index" = Table.AddIndexColumn(Source, "Row Number" ,1),
  #"Kept Errors" = Table.SelectRowsWithErrors(#"Added Index", {"Master Customer Name (Inc)", "Master Customer Number (Inc)", "Incident Identifier (Inc)", "Last WO Number (Inc)", "Assigned To Identifier (Inc)", "Country Code (Inc)", "Incident Start Date Time TZ (Inc)", "Incident Start Date TZ", "Incident End Date Time TZ (Inc)", "Incident End Date TZ", "Problem Tier 1 Description (Inc)", "Problem Tier 2 Description (Inc)", "Problem Tier 3 Description (Inc)", "Problem Tier 4 Description (Inc)", "Resolution Tier 1 Description (Inc)", "Resolution Tier 2 Description (Inc)", "Site Postal Code (Inc)", "Store Branch Name (Inc)", "Total Activity Minutes (Inc)"}),
#"Reordered Columns" = Table.ReorderColumns(#"Kept Errors", {"Row Number", "Master Customer Name (Inc)", "Master Customer Number (Inc)", "Incident Identifier (Inc)", "Last WO Number (Inc)", "Assigned To Identifier (Inc)", "Country Code (Inc)", "Incident Start Date Time TZ (Inc)", "Incident Start Date TZ", "Incident End Date Time TZ (Inc)", "Incident End Date TZ", "Problem Tier 1 Description (Inc)", "Problem Tier 2 Description (Inc)", "Problem Tier 3 Description (Inc)", "Problem Tier 4 Description (Inc)", "Resolution Tier 1 Description (Inc)", "Resolution Tier 2 Description (Inc)", "Site Postal Code (Inc)", "Store Branch Name (Inc)", "Total Activity Minutes (Inc)"})
in
  #"Reordered Columns"

Does it existe in PBI

2017-03-08_13h50_00.jpg

Sorry for this,

 

let
    Source = Excel.Workbook(File.Contents("..............................."), null, true),
    #"Report 1_Sheet" = Source{[Item="Report 1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(#"Report 1_Sheet", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Master Customer Name (Inc)", type text}, {"Master Customer Number (Inc)", Int64.Type}, {"Incident Identifier (Inc)", Int64.Type}, {"Last WO Number (Inc)", type text}, {"Assigned To Identifier (Inc)", type text}, {"Country Code (Inc)", type text}, {"Incident Start Date Time TZ (Inc)", type datetime}, {"Incident Start Date TZ", type date}, {"Incident End Date Time TZ (Inc)", type datetime}, {"Incident End Date TZ", type date}, {"Problem Tier 1 Description (Inc)", type text}, {"Problem Tier 2 Description (Inc)", type text}, {"Problem Tier 3 Description (Inc)", type text}, {"Problem Tier 4 Description (Inc)", type text}, {"Resolution Tier 1 Description (Inc)", type text}, {"Resolution Tier 2 Description (Inc)", type text}, {"Site Postal Code (Inc)", Int64.Type}, {"Store Branch Name (Inc)", type text}, {"Total Activity Minutes (Inc)", Int64.Type}})
in
    #"Changed Type"

Anonymous
Not applicable

allright

 

Change this piece of the code

    #"Changed Type" =
        Table.TransformColumnTypes(#"Promoted Headers",
        {{"Master Customer Name (Inc)", type text},

Spoiler
        {"Master Customer Number (Inc)", Int64.Type},

      

 

 

to

{"Master Customer Number (Inc)", type text},

 

 

I found how to make that it's working. Maybe it's the same thing like you wrote me, but I'm glad that it's working well for me.

 

In Advanced Editor, "{{"Master Customer Name (Inc)", type text}" I replace "text type" by "general", same option for data type that you can find in Excel, but not in PBI.

So, when I pressed save, it's give me an error code, but at this moment I press on remove "changed type", and this is what's changing my data type in ANY, what I need actualy, it's same as general in Excel. So, I tested it, and it's show me now in my table all informations, even in filter, when I press now "Load More" it's show me all what I need.

 

Thanks a lot @Anonymous

 

Anonymous
Not applicable

ok. that's the code of the error report.

 I need in addition the code of the original "Report1".

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.