Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
GoodeD
Frequent Visitor

Newbie question

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!

 

1 ACCEPTED SOLUTION
JirkaZ
Solution Specialist
Solution Specialist

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.

View solution in original post

15 REPLIES 15
pranit828
Community Champion
Community Champion

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

pranit828_0-1675971695028.png

 





PBI_SuperUser_Rank@1x.png


Hope it resolves your issue? 
Did I answer your question? Mark my post as a solution!

Appreciate your Kudos, Press the thumbs up button!!
Linkedin Profile

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
Frequent Visitor

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

GoodeD
Frequent Visitor

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
Solution Specialist
Solution Specialist

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

GoodeD
Frequent Visitor

So embarrasing- but where do I find that

 

JirkaZ
Solution Specialist
Solution Specialist

JirkaZ_0-1675972759051.pngJirkaZ_1-1675972790230.png

 

GoodeD
Frequent Visitor

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
Solution Specialist
Solution Specialist

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
Frequent Visitor

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
Solution Specialist
Solution Specialist

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
Frequent Visitor

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

GoodeD
Frequent Visitor

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
Frequent Visitor

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

JirkaZ
Solution Specialist
Solution Specialist

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.