Newbie question

[GoodeD]

Hi - I was hoping for some help -

I put together my very first desktop power bi with sales, distributor and product data pertaining to commissions from our sales reps around the United States.

I am loving power bi and creating reports my company is loving for all of 2022 ----- BUT - I cannot get my 2023 csv files to load. If there is a date from 2021 or 2022 it will load - but skips over the 2023. I don't think I have any filters on - I have checked all I know how ---

any ideas? Please help!

[pranit828]

Hi @GoodeD ,
Save the excel / csv file and then try refreshing the repoort .

[GoodeD]

I did that but problem persists.  Could I have added a filter accidentally? I checked the data lookup file and all source files and can't find anything.  

[GoodeD]

If I add data with a 2021 or 2022 date it will update correctly, but bypass any with 2023 date

[GoodeD]

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.

[JirkaZ]

Can you share the Power Query code for the source definition? That may help us troubleshoot.

[GoodeD]

So embarrasing- but where do I find that

[JirkaZ]

[GoodeD]

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"

[JirkaZ]

As you can see this piece of code doesn't reference any Excel nor CSV file igestion. 

Instead it's referring to "Sales Database" source. So I'll need to see the Power Query code for that source too.

[GoodeD]

I think I meant to send this - thanks for your patience

let
Source = Csv.Document(File.Contents("C:\Users\gdethero\OneDrive - TMT America\COMMISSIONS\2023 SOURCE FILES\Ongoing SALES DATABASE.csv"),[Delimiter=",", Columns=18, Encoding=65001, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Sales Key", type text}, {"ND Name ", type text}, {"ND Key", type text}, {"Distributor Key", Int64.Type}, {"Dist Name", type text}, {"SKU", type text}, {"Product Key", Int64.Type}, {"QTY", Int64.Type}, {"Price", Currency.Type}, {"Account#", Int64.Type}, {"Account Name", type text}, {"ShipTo - City", type text}, {"Zip", Int64.Type}, {"State Key", type text}, {"State", type text}, {"RBM Key ", Int64.Type}, {"RBM Name ", type text}, {"Date", type date}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Sales Key", "ND Name ", "ND Key", "Account#"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"Zip", Int64.Type}}),
#"Duplicated Column" = Table.DuplicateColumn(#"Changed Type1", "Date", "Date - Copy"),
#"Renamed Columns" = Table.RenameColumns(#"Duplicated Column",{{"Date - Copy", "Month"}}),
#"Changed Type2" = Table.TransformColumnTypes(#"Renamed Columns",{{"Month", type date}}),
#"Extracted Month" = Table.TransformColumns(#"Changed Type2",{{"Month", Date.Month, Int64.Type}}),
#"Filtered Rows" = Table.SelectRows(#"Extracted Month", each ([Month] = 12)),
#"Changed Type3" = Table.TransformColumnTypes(#"Filtered Rows",{{"QTY", type number}}),
#"Added Conditional Column" = Table.AddColumn(#"Changed Type3", "Custom", each if [Product Key] = 31 then "C" else if [Product Key] = 30 then "C" else if [Product Key] = 33 then "C" else if [Product Key] = 83 then "C" else if [Product Key] = 3 then "A" else if [Product Key] = 19 then "A" else if [Product Key] = 9 then "A" else if [Product Key] = 10 then "A" else if [Product Key] = 1 then "A" else if [Product Key] = 2 then "A" else if [Product Key] = 52 then "A" else if [Product Key] = 104 then "A" else if [Product Key] = 53 then "A" else if [Product Key] = 131 then "A" else "B"),
#"Renamed Columns1" = Table.RenameColumns(#"Added Conditional Column",{{"Custom", "A B C Products"}}),
#"Duplicated Column1" = Table.DuplicateColumn(#"Renamed Columns1", "Date", "Date - Copy"),
#"Changed Type4" = Table.TransformColumnTypes(#"Duplicated Column1",{{"Date - Copy", type date}}),
#"Extracted Year" = Table.TransformColumns(#"Changed Type4",{{"Date - Copy", Date.Year, Int64.Type}}),
#"Removed Columns1" = Table.RemoveColumns(#"Extracted Year",{"Month"})
in
#"Removed Columns1"

[JirkaZ · Accepted solution]

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.

[GoodeD]

Got it finally! Thank you sooooooooo much!!!! 

[GoodeD]

how can I remove the one filter without messing up eveything else - I received an expression error when I tried to delete just that line -- Expression.Error: The import Filtered Rows matches no exports. Did you miss a module reference

[GoodeD]

Ok, this is very helpful. Thank you - I will see if I can remove that filter

[JirkaZ]

This will require more details. How and from where are you loading the csv files?