Forum Discussion
Newbie question
Ok - so you can see the file it's referring to. Also you can see that it's taking only the first 18 columns from that file.
And one final thing you can see is that there is one filter set up and it filters the data to only rows where [Month] = 12.
Now I don't know what the logic behind that is, but that is what's set up. It might be just the final year's data... don't know.
OK you are right. I am adding it to an existing spreadsheet that is already being utilized in power bi. I use the transform data button, data source settings, and then making sure it is on correct sheet.
Can you share the Power Query code for the source definition? That may help us troubleshoot.
So embarrasing- but where do I find that
let
Source = #"Sales Database",
#"Detected Type Mismatches" = let
tableWithOnlyPrimitiveTypes = Table.SelectColumns(Source, Table.ColumnsOfType(Source, {type nullable number, type nullable text, type nullable logical, type nullable date, type nullable datetime, type nullable datetimezone, type nullable time, type nullable duration})),
recordTypeFields = Type.RecordFields(Type.TableRow(Value.Type(tableWithOnlyPrimitiveTypes))),
fieldNames = Record.FieldNames(recordTypeFields),
fieldTypes = List.Transform(Record.ToList(recordTypeFields), each [Type]),
pairs = List.Transform(List.Positions(fieldNames), (i) => {fieldNames{i}, (v) => if v = null or Value.Is(v, fieldTypes{i}) then v else error [Message = "The type of the value does not match the type of the column.", Detail = v], fieldTypes{i}})
in
Table.TransformColumns(Source, pairs),
#"Added Index" = Table.AddIndexColumn(#"Detected Type Mismatches", "Row Number" ,1),
#"Kept Errors" = Table.SelectRowsWithErrors(#"Added Index", {"Distributor Key", "Dist Name", "SKU", "Product Key", "QTY", "Price", "Account Name", "ShipTo - City", "Zip", "State Key", "State", "RBM Key ", "RBM Name ", "Date", "A B C Products", "Date - Copy"}),
#"Reordered Columns" = Table.ReorderColumns(#"Kept Errors", {"Row Number", "Distributor Key", "Dist Name", "SKU", "Product Key", "QTY", "Price", "Account Name", "ShipTo - City", "Zip", "State Key", "State", "RBM Key ", "RBM Name ", "Date", "A B C Products", "Date - Copy"})
in
#"Reordered Columns"