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