Odd Table.Combine behavior - and ideas?

[Anonymous]

Hey Hive-mind - have an odd one for you:

I have one Sales query that imports data from an Azure SQL DB.  I have a series of Key columns that are all whole numbers.

I have a second InternationalSales query that imports from a folder and combines files together that are the same schema as the DB table I'm importing from with the same Key columns that are all whole numbers.

As soon as I append InternationalSales to Sales - the code is literally "= Table.Combine({dbo_Sales, #"Non-US Sales"})" - it changes all my Key columns to decimals.  In fact, it changes every whole number column to a decimal.  Even though they are absolutely set to whole numbers in both queries prior to the append.

Has anyone seen this before?  It's not stopping any development as I can add a ChangedType step and set them back to whole numbers but it's weird and a pain.  Thoughts?

[Anonymous]

So there's the oddity - there *aren't* any ChangeType steps other than the one for the flat files which is required or everything comes thru as Any datatype.

Here's the code of the SQL import:

let
Source = Sql.Database("<<removed connection string>>", "ContosoSales", [CreateNavigationProperties=false]),
dbo_vSales = Source{[Schema="dbo",Item="vSales"]}[Data]
in
dbo_vSales

Here's the code for the import from the folder:

let
Source = Folder.Files("<<removed folder name>>"),
#"Filtered Rows" = Table.SelectRows(Source, each ([Extension] = ".csv") and ([Name] <> "Inventory.csv")),
#"Filtered Hidden Files1" = Table.SelectRows(#"Filtered Rows", each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"SalesKey", Int64.Type}, {"Date", type date}, {"ChannelKey", Int64.Type}, {"StoreKey", Int64.Type}, {"ProductKey", Int64.Type}, {"PromotionKey", Int64.Type}, {"CurrencyKey", Int64.Type}, {"Unit Cost", type number}, {"Unit Price", type number}, {"Sales Quantity", Int64.Type}, {"Return Quantity", Int64.Type}, {"Return Amount", type number}, {"Discount Quantity", Int64.Type}, {"Discount Amount", type number}, {"Total Cost", type number}, {"Sales Amount", type number}, {"ETLLoadID", Int64.Type}, {"LoadDate", type date}, {"UpdateDate", type date}, {"GeographyKey", Int64.Type}})
in
#"Changed Type"

Again - the ChangedType step is required for the flat files or everything would just be "Any".

Here's the code from the Append step:

let
Source = Sql.Database("<<removed connection string>>", "ContosoSales", [CreateNavigationProperties=false]),
dbo_vSales = Source{[Schema="dbo",Item="vSales"]}[Data],
#"Appended Query" = Table.Combine({dbo_vSales, #"Non-US Sales"})
in
#"Appended Query"

And magically all my whole numbers are now decimals.

????

🙂

[lbendlin]

Ha! I didn't realize that you have two different sources, one from SQL server and one from csv.  Did you know that Power BI pulls the field format from SQL server?  I bet there's a mismatch between that and the format on the csv (which is basically "variant")

The moment Table.Combine sees different formats (or slightly different casing) it goes crazy.

[lbendlin]

Can you place your Table.Combine above the very first Change Type step, or eliminate any existing Change Type steps and see if that changes anything?

I generally disallow Power BI messing with my column types or table relationships. I can set these myself, thank you very much.