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