Data Format Error: contain dash "-"

[yc-bsa]

Hello all, 

I am trying to load my data into Excel but have error with one of the columns (please see below). 

I tried to put the data type as 'any' but still shows error. I tried using replace, but it doesn't work if the data type is 'any' (i.e. it shows an error message when I type "-" in find value).

If I change data type to 'text', the function will go through (i.e. system let me put in find "-") but nothing would happen (i.e. doesn't replace anything). 

Would anyone know what I should do here? 

Thanks heaps. 

[BA_Pete]

Hi @yc-bsa ,

I'm not entirely clear what you're asking here. Are you trying to do a text replacement in Power Query but it's not working?

In terms of the data types, it looks like the column you are referencing should be TEXT type. You shouldn't export anything out of Power Query as ANY type.

Once you've changed the data type to text, try and do whatever it is you are trying to do and update here with clear details as to what you have tried (M code included please) and what error you get, or why the results aren't as you expect.

Pete

[yc-bsa]

Hi @BA_Pete ,

Thanks for your reply and suggestion. 

I changed the column to type TEXT and tried exporting but it came back with the same error message. 

Column 'Project' where I am having problem with - the cells where it comes up as error, the content is in the format 'AAA-111' (without ' '). 

This same error message comes up when I change column to type TEXT or type ANY. 

I just want to export the file as they are. 

[BA_Pete]

Hi @yc-bsa ,

Can you provide your M query please?

Select the query that you're having trouble with, open Advanced Editor and copy out everything in there and paste into a code window ( </> button) here. Remove sensitive items from the source step, such as file/server paths.

My guesses so far:

1) there may be a function later in your M code that is trying to convert this field back to a number.

2) the Excel destination may be preformatted as number type, so the error is throwing there rather than in Power Query.

Pete

[yc-bsa]

Hi @BA_Pete ,

Thanks for your reply. 

Do you need the full M Code from the beginning? Or the M code for the column that I am having trouble with? For the later, please see below:

<#"Changed Type2" = Table.TransformColumnTypes(#"Filtered Rows4",{{"Project", type text}})>
This is the last line of the codes and I am trying to export the query after this. 

Cheers

[BA_Pete]

Hi @yc-bsa ,

Can you copy/paste your whole M code query from Advanced Editor here please? I think you are trying to convert this field to a number earlier in your query and the error is carrying forward to the end of the query.

When you paste the code here, hit this button and paste in there to keep everything nice and tidy/readable:

Pete

[yc-bsa]

Hi @BA_Pete 

Thanks I see. 

    #"Filtered Rows" = Table.SelectRows(Source, each true),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Content", "Name"}),
    #"Added Custom" = Table.AddColumn(#"Removed Other Columns", "Table", each Excel.Workbook([Content])),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Content"}),
    #"Expanded Table" = Table.ExpandTableColumn(#"Removed Columns", "Table", {"Name", "Data", "Item", "Kind", "Hidden"}, {"Name.1", "Data", "Item", "Kind", "Hidden"}),
    #"Filtered Rows1" = Table.SelectRows(#"Expanded Table", each ([Kind] = "Sheet")),
    #"Removed Other Columns1" = Table.SelectColumns(#"Filtered Rows1",{"Name", "Name.1", "Data"}),
    #"Expanded Data" = Table.ExpandTableColumn(#"Removed Other Columns1", "Data", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17", "Column18", "Column19", "Column20", "Column21", "Column22", "Column23", "Column24", "Column25", "Column26", "Column27"}, {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17", "Column18", "Column19", "Column20", "Column21", "Column22", "Column23", "Column24", "Column25", "Column26", "Column27"}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Expanded Data", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"010122-100122.xlsx", type text}, {"00000673", Int64.Type}, {"GL Account", type text}, {"Date", type date}, {"Type", type text}, {"Reference", type text}, {"Details", type text}, {"Item Details", type any}, {"Amount", type number}, {"Batch Ref", type text}, {"Trans No.", Int64.Type}, {"Sub Ledger", type any}, {"Description", type any}, {"Other Side", type text}, {"Special A/C", type text}, {"Project", Int64.Type}, {"WHSE", type any}, {"User", type text}, {"Audit Date", type date}, {"Audit Time", type datetime}, {"Created By", type text}, {"Tracking Type", type text}, {"Tracking ID", Int64.Type}, {"Tracking Suffix", type any}, {"Tracking Seq", Int64.Type}, {"Financial Period", Int64.Type}, {"Financial Year", Int64.Type}, {"Customer Code", type text}, {"Customer Name", type text}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Changed Type",{"00000673"}),
    #"Filtered Rows2" = Table.SelectRows(#"Removed Columns1", each not Text.Contains([GL Account], "GL Account")),
    #"Added Custom1" = Table.AddColumn(#"Filtered Rows2", "Code", each [GL Account]),
    #"Extracted Last Characters" = Table.TransformColumns(#"Added Custom1", {{"Code", each Text.End(_, 4), type text}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Extracted Last Characters",{{"Code", Int64.Type}}),
    #"Filtered Rows3" = Table.SelectRows(#"Changed Type1", each [Code] < 2000),
    #"Removed Columns2" = Table.RemoveColumns(#"Filtered Rows3",{"010122-100122.xlsx"}),
    #"Added Custom2" = Table.AddColumn(#"Removed Columns2", "Cost Center", each [GL Account]),
    #"Extracted First Characters" = Table.TransformColumns(#"Added Custom2", {{"Cost Center", each Text.Start(_, 4), type text}}),
    #"Merged Queries" = Table.NestedJoin(#"Extracted First Characters", {"Cost Center"}, Sheet1, {"APS FY22"}, "Sheet1", JoinKind.LeftOuter),
    #"Expanded Sheet1" = Table.ExpandTableColumn(#"Merged Queries", "Sheet1", {"APS"}, {"Sheet1.APS"}),
    #"Filtered Rows4" = Table.SelectRows(#"Expanded Sheet1", each ([Sheet1.APS] = "APS")),
    #"Changed Type2" = Table.TransformColumnTypes(#"Filtered Rows4",{{"Project", type any}})

[yc-bsa]

Hello Pete, 

I tookd your advice and checked the below line, the column I am having problem with is 'Project' so instead of int64.type, I change it to  type TEXT. The column is not showing error anymore. Thanks for that. 

However, I am still having the same error message when I tried to load the file.

I also have an error sign next to the transform sample file, not sure if that's relevant. 

 #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"010122-100122.xlsx", type text}, {"00000673", Int64.Type}, {"GL Account", type text}, {"Date", type date}, {"Type", type text}, {"Reference", type text}, {"Details", type text}, {"Item Details", type any}, {"Amount", type number}, {"Batch Ref", type text}, {"Trans No.", Int64.Type}, {"Sub Ledger", type any}, {"Description", type any}, {"Other Side", type text}, {"Special A/C", type text}, {"Project", Int64.Type}, {"WHSE", type any}, {"User", type text}, {"Audit Date", type date}, {"Audit Time", type datetime}, {"Created By", type text}, {"Tracking Type", type text}, {"Tracking ID", Int64.Type}, {"Tracking Suffix", type any}, {"Tracking Seq", Int64.Type}, {"Financial Period", Int64.Type}, {"Financial Year", Int64.Type}, {"Customer Code", type text}, {"Customer Name", type text}}),

[BA_Pete]

Hi @yc-bsa ,

Is that code in your last post from the Transform Sample File query?

If it is, then it's the exact same problem:

{"Project", Int64.Type} should be {"Project", type text}

If not, then you'll need to post the whole M code from the Transform Sample File query so I can take a look.

Pete

[BA_Pete]

Hi @yc-bsa ,

That's perfect, thanks.

It's what I thought: you're changing the data type earlier in your query and carrying the error forward. In fact, I don't think it's you that's evn done this, I think Power Query has automatically done this for you when you did the Promote Headers step. At this point, PQ evaluates the top N number of rows in your table and tries to 'guess' the correct data type for you. In this instance, it's got the guess wrong.

In Advanced Editor, find your #"Changed Type" step, find the part that's highlighted in blue below, and change the 'Int64.Type' text to 'type text' instead, so it looks like exactly like this:

{"Project", type text}

Once you've done that, delete your '#"Changed Type2"' step at the end of the query.

Pete

[yc-bsa]

Hi @BA_Pete ,

Please see below code: i didn't change much but did change the type for column 'PROJECT' to TEXT, and deleted the later step. 

 #"Filtered Rows" = Table.SelectRows(Source, each true),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Name", "Content"}),
    #"Added Custom" = Table.AddColumn(#"Removed Other Columns", "Custom", each Excel.Workbook([Content])),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Name", "Data", "Item", "Kind", "Hidden"}, {"Name.1", "Data", "Item", "Kind", "Hidden"}),
    #"Filtered Rows1" = Table.SelectRows(#"Expanded Custom", each ([Kind] = "Sheet")),
    #"Removed Other Columns1" = Table.SelectColumns(#"Filtered Rows1",{"Name.1", "Data"}),
    #"Expanded Data" = Table.ExpandTableColumn(#"Removed Other Columns1", "Data", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17", "Column18", "Column19", "Column20", "Column21", "Column22", "Column23", "Column24", "Column25", "Column26", "Column27"}, {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17", "Column18", "Column19", "Column20", "Column21", "Column22", "Column23", "Column24", "Column25", "Column26", "Column27"}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Expanded Data", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"00000673", Int64.Type}, {"GL Account", type text}, {"Date", type date}, {"Type", type text}, {"Reference", type text}, {"Details", type text}, {"Item Details", type any}, {"Amount", type number}, {"Batch Ref", type text}, {"Trans No.", Int64.Type}, {"Sub Ledger", type any}, {"Description", type any}, {"Other Side", type text}, {"Special A/C", type text}, {"Project", type text}, {"WHSE", type any}, {"User", type text}, {"Audit Date", type date}, {"Audit Time", type datetime}, {"Created By", type text}, {"Tracking Type", type text}, {"Tracking ID", Int64.Type}, {"Tracking Suffix", type any}, {"Tracking Seq", Int64.Type}, {"Financial Period", Int64.Type}, {"Financial Year", Int64.Type}, {"Customer Code", type text}, {"Customer Name", type text}}),
    #"Filtered Rows2" = Table.SelectRows(#"Changed Type", each not Text.Contains([GL Account], "GL Account")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows2",{"00000673"}),
    #"Added Custom1" = Table.AddColumn(#"Removed Columns", "Cost Center", each [GL Account]),
    #"Split Column by Position" = Table.SplitColumn(#"Added Custom1", "Cost Center", Splitter.SplitTextByRepeatedLengths(4), {"Cost Center.1", "Cost Center.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Position",{{"Cost Center.1", type text}, {"Cost Center.2", Int64.Type}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type2",{{"Cost Center.1", "Cost Center"}, {"Cost Center.2", "Ledger Code"}}),
    #"Filtered Rows3" = Table.SelectRows(#"Renamed Columns", each [Ledger Code] < 2000),
    #"Merged Queries" = Table.NestedJoin(#"Filtered Rows3", {"Cost Center"}, Sheet1, {"APS FY22"}, "Sheet1", JoinKind.LeftOuter),
    #"Expanded Sheet1" = Table.ExpandTableColumn(#"Merged Queries", "Sheet1", {"APS"}, {"Sheet1.APS"}),
    #"Filtered Rows4" = Table.SelectRows(#"Expanded Sheet1", each ([Sheet1.APS] = "APS"))

But when I tried to load the data, it still shows the below error message. 

[BA_Pete]

Hi @yc-bsa ,

Can you try sending the table from Power Query to a completely new tab in your workbook please? I'm wondering whether there's formats on the tab you're currently trying to send to that are causing this conflict.

If that doesn't work, are you able to share the whole Excel file? I think I'm going to struggle to find the deeper issue through screenshots and code snippets.

If you can share, please ensure the source is accessible i.e. not an SQL server or something that I can't access, as this will prevent the whole query from loading.

Also remove any sensitive information.

Even if you can provide the source data, I can reproduce your steps and see if I get the same error.

Pete