Forum Discussion
Data Format Error: contain dash "-"
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
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
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
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}})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