Forum Discussion
Filter YTD calculation
- 6 years ago
- ktt7776 years agoHelper V
Great! Thanks, Ashish
- Ashish_Mathur6 years agoSuper User
You are welcome.
- ktt7776 years agoHelper V
Hi , sorry i have another problem.
I only try to update dashboard by refresh data but there is an error:
The column 'System: Production Report: Current Stock Report.rdl Version:1.0.0' of the table wasn't found.
I don't know what is going on. I do not change anything and data format is the same.
Here is my advanced editor
Source = Folder.Files("C:\Users\xxx\Current Stock Report"),
#"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File from Current Stock Report", each #"Transform File from Current Stock Report"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File from Current Stock Report"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File from Current Stock Report", Table.ColumnNames(#"Transform File from Current Stock Report"(#"Sample File"))),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"System: Production Report: Current Stock Report.rdl Version:1.0.0", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type any}, {"Column8", type any}, {"Column9", type text}, {"Column10", type text}, {"Column11", type any}, {"Column12", type text}, {"Column13", type text}, {"Column14", type any}, {"Column15", type text}, {"Column16", type any}, {"Column17", type any}, {"Column18", type any}, {"Column19", type text}, {"Column20", type text}, {"Column21", type any}, {"Column22", type text}, {"Column23", type any}, {"Column24", type any}, {"Column25", type any}, {"Column26", type any}, {"Column27", type any}, {"Column28", type any}, {"Column29", type any}, {"Column30", type any}}),
#"Removed Top Rows" = Table.Skip(#"Changed Type",4),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Current Stock Report.xlsx", type text}, {"Region", type text}, {"Location", type text}, {"Column4", type text}, {"Warehouse Name", type text}, {"Product Category Name", type text}, {"Product Sub Category Name", type text}, {"Column8", type any}, {"Column9", type any}, {"Product Code", type text}, {"Product Name", type text}, {"Column12", type any}, {"Specification", type text}, {"Batch Number", type text}, {"Expiry Date", type date}, {"SOF", Int64.Type}, {"SOF End Date", type date}, {"Effective End Date", type date}, {"Final Posting Date", type date}, {"DEA Code", Int64.Type}, {"Project Code", Int64.Type}, {"Quantity On Hand", Int64.Type}, {"Currency Name", type text}, {"Unit Value", type number}, {"Unit Value (Dollars)", type number}, {"GIK", type logical}, {"Program / Admin Stock", type logical}, {"Pre Positioned", type logical}, {"In Transit", type logical}, {"Updated Date Time", type datetime}, {"Total Value (Dollars)", type number}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Column4", "Column8", "Column9", "Column12"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Removed Columns",{{"DEA Code", type text}, {"SOF", type text}}),
#"Replaced Errors" = Table.ReplaceErrorValues(#"Changed Type2", {{"SOF", null}, {"SOF End Date", null}, {"Effective End Date", null}, {"Final Posting Date", null}, {"DEA Code", null}, {"Project Code", null}, {"Quantity On Hand", null}, {"Currency Name", null}, {"Unit Value", null}, {"Unit Value (Dollars)", null}, {"GIK", null}, {"Program / Admin Stock", null}, {"Pre Positioned", null}, {"In Transit", null}, {"Updated Date Time", null}, {"Total Value (Dollars)", null}})
in
#"Replaced Errors"any idea what is happening :D?
Thank you