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.
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"
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, this is very helpful. Thank you - I will see if I can remove that filter
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
Got it finally! Thank you sooooooooo much!!!!