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