let
Source = Folder.Files(Folder_Path_Flash_Reports("Folders_Paths", 1)),
#"Added Custom" = Table.AddColumn(Source, "Year", each Number.FromText(Text.Start([Name],4)), type number),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Content", "Date accessed", "Date modified", "Date created", "Attributes", "Extension"}),
GetColumnNamesTables = Table.AddColumn(#"Removed Columns", "ColumnsListsToRename", each GetStructureColumnNamesTable([Year])),
Get_Tables_From_Files = Table.AddColumn(GetColumnNamesTables, "Flash_Report_Table", each Table.Buffer(Get_Data_from_file([Folder Path]&[Name],"Table2"))),
#"Get_Tables_From_Files_(Renamed)" = Table.AddColumn(Get_Tables_From_Files, "Flash_Report_Table_(Renamed)", each RenameColumns([Flash_Report_Table],[ColumnsListsToRename])),
#"Removed Table with old names" = Table.RenameColumns(Table.RemoveColumns(#"Get_Tables_From_Files_(Renamed)",{"Flash_Report_Table"}),{{"Flash_Report_Table_(Renamed)", "Flash_Report_Table"}}),
Column_Names_List_Added = Table.AddColumn(#"Removed Table with old names", "Columns_Names", each Table.ColumnNames([Flash_Report_Table])),
Get_unique_Names_of_all_tables = Table.Distinct(Table.ExpandListColumn(Table.RemoveColumns(Column_Names_List_Added,{"Name", "Folder Path", "Year", "Flash_Report_Table", "ColumnsListsToRename"}), "Columns_Names"))[Columns_Names],
Remove_waste_columns = Table.RemoveColumns(Column_Names_List_Added,{"ColumnsListsToRename", "Folder Path", "Columns_Names"}),
Month_to_find_list = {"01","02","03","04","05","06","07","08","09","10","11","12","Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"},
Full_Columns_Names_Set = Table.Buffer(Table.FromList(Get_unique_Names_of_all_tables, Splitter.SplitByNothing(), null, null, ExtraValues.Error)),
#"Step1 - Month" = Table.AddColumn(Full_Columns_Names_Set, "Check1 - Month", (C) => List.AnyTrue(List.Transform(Month_to_find_list, each Text.Contains(C[Column1], _)))),
GetValuesColumnNames = List.Buffer(Table.ToList(Table.Distinct(Table.RemoveColumns(Table.TransformColumnTypes(Table.Distinct(Table.ReorderColumns(Table.RemoveColumns(Table.UnpivotOtherColumns(Table.SelectRows(Excel.CurrentWorkbook(){[Name="ColumnNames"]}[Content], each ([Curr Key] <> null)),{"Columns in the report", "Curr Key", "Data Type"}, "FR_Year", "FR_Columns_Names"),{"Curr Key", "Data Type"}),{"Columns in the report", "FR_Columns_Names", "FR_Year"})),{{"Columns in the report", type text}, {"FR_Columns_Names", type text}, {"FR_Year", Int64.Type}}),{"Columns in the report", "FR_Year"})))),
#"Step2 - FR_Values_Names" = Table.AddColumn(#"Step1 - Month", "Check2 - FR_Columns_Names", (C) => List.AnyTrue(List.Transform(GetValuesColumnNames, each Text.Contains(C[Column1], _)))),
GetCurrKeyList = List.Buffer(Table.ToList(Table.Distinct(Table.SelectColumns(Table.SelectRows(Excel.CurrentWorkbook(){[Name="ColumnNames"]}[Content], each ([Curr Key] <> null)),{"Curr Key"})))),
#"Step3 - Curr_Key" = Table.AddColumn(#"Step2 - FR_Values_Names", "Check3 - Currency_Key", (C) => List.AnyTrue(List.Transform(GetCurrKeyList, each Text.Contains(C[Column1], _)))),
GetStructureColumnsNames = List.Buffer(Table.ToList(Table.SelectColumns(Table.SelectRows(Excel.CurrentWorkbook(){[Name="ColumnNames"]}[Content], each ([Curr Key] = null)),{"Columns in the report"}))),
#"Step4 - Structure" = Table.AddColumn(#"Step3 - Curr_Key", "Check4 - Structure", (C) => List.AnyTrue(List.Transform(GetStructureColumnsNames, each List.Contains(GetStructureColumnsNames,C[Column1])))),
Selected_Columns_Names = Table.SelectColumns(Table.SelectRows(Table.AddColumn(#"Step4 - Structure", "Final Column Check", each if ([#"Check1 - Month"] = true and [#"Check2 - FR_Columns_Names"] = true and [#"Check3 - Currency_Key"] = true) then true else if ([#"Check1 - Month"] = false and [#"Check4 - Structure"] = true) then true else false), each ([Final Column Check] = true)),{"Column1"})[Column1],
FL_Columns_Selected = Table.RemoveColumns(Table.AddColumn(Remove_waste_columns, "FL_Table_Selected_Columns", each Table.SelectColumns([Flash_Report_Table], Selected_Columns_Names, MissingField.Ignore)),{"Flash_Report_Table"}),
Reordered_All_Columns = Table.RemoveColumns(
Table.AddColumn(
FL_Columns_Selected, "Unpivoted", each
Table.RenameColumns(
Table.Pivot(
Table.RemoveColumns(
Table.AddColumn(
Table.AddColumn(
Table.ReplaceValue(
Table.UnpivotOtherColumns(
[FL_Table_Selected_Columns], GetListOfStructureColumns([Year]), "Attribute", "Value"
)
,"-",0,Replacer.ReplaceValue,{"Value"}
)
, "Fiscal_Month", each Text.Trim(Extract_Months([Attribute]))
)
, "Attribute_Cleared", each Text.Combine( List.ReplaceMatchingItems(Text.ToList ([Attribute]), { {",", ""}, {".", ""}, {"0", ""}, {"1", ""}, {"2", ""}, {"3", ""}, {"4", ""}, {"5", ""}, {"6", ""}, {"7", ""}, {"8", ""}, {"9", ""} }))
)
,{"Attribute"}
)
, {"01","02","03","04","05","06","07","08","09","10","11","12"}, "Fiscal_Month", "Value",List.Sum
)
,{{"01", "M01"}, {"02", "M02"}, {"03", "M03"}, {"04", "M04"}, {"05", "M05"}, {"06", "M06"}, {"07", "M07"}, {"08", "M08"}, {"09", "M09"}, {"10", "M10"}, {"11", "M11"}, {"12", "M12"}}
)
),{"FL_Table_Selected_Columns"}
),
Expand_Table = Table.RemoveColumns(Table.ExpandTableColumn(Reordered_All_Columns, "Unpivoted", List.Combine({GetStructureColumnsNames,{"Attribute_Cleared"}, {"Value"}, {"Fiscal_Month"},{"M01"}, {"M02"}, {"M03"}, {"M04"}, {"M05"}, {"M06"}, {"M07"}, {"M08"}, {"M09"}, {"M10"}, {"M11"}, {"M12"}}), List.Combine({GetStructureColumnsNames, {"Attribute_Cleared"}, {"Value"}, {"Fiscal_Month"}, {"M01"}, {"M02"}, {"M03"}, {"M04"}, {"M05"}, {"M06"}, {"M07"}, {"M08"}, {"M09"}, {"M10"}, {"M11"}, {"M12"}})),{"Value", "Fiscal_Month"}),
#"Changed Data Types" = Table.TransformColumnTypes(Table.TransformColumnTypes(Expand_Table, List.Transform(List.Combine({GetStructureColumnsNames, {"Attribute_Cleared"}}), each {_, type text})),{{"M01", type number}, {"M02", type number}, {"M03", type number}, {"M04", type number}, {"M05", type number}, {"M06", type number}, {"M07", type number}, {"M08", type number}, {"M09", type number}, {"M10", type number}, {"M11", type number}, {"M12", type number}}),
Data_type_Column = Table.TransformColumnTypes(Table.AddColumn(#"Changed Data Types", "Data_Type", each "Flash_Reports_ADV"),{{"Data_Type", type text}})
in
Data_type_Column
//Функции
================================================================
* Folder_Path_Flash_Reports
let Parameter=(TableName as text, RowNumber as number) =>
let
Source = Excel.CurrentWorkbook(){[Name=TableName]}[Content],
value = Source{RowNumber-1}[Folder Path]
in
value
in Parameter
================================================================
* Get_Year_From_FilePath
(filepath)=>
let
GetCurrentYearTable = Table.FromRecords({[FilePath=filepath]}),
GetCurrentYear = Table.AddColumn(Table.SelectColumns(Record.ToTable(Table.Last(Table.Transpose(Table.SplitColumn(Table.FromRecords({[FilePath=filepath]}), "FilePath", Splitter.SplitTextByDelimiter("\", QuoteStyle.Csv))))), "Value"), "Custom", each Text.Start([Value],4)){0}[Custom]
in
GetCurrentYear
================================================================
* Get_Data_from_file
(filepath, tableName)=>
let
Source = Excel.Workbook(File.Contents(filepath), null, true),
Table1_Table = Source{[Item=tableName,Kind="Table"]}[Data]
in
Table1_Table
================================================================
* GetStructureColumnNamesTable
(FlashReportYear)=>
let
Source = Table.ToColumns(Table.Transpose(Table.ReorderColumns(Table.RemoveColumns(Table.SelectRows(Table.TransformColumnTypes(Table.UnpivotOtherColumns(Excel.CurrentWorkbook(){[Name="ColumnNames"]}[Content], {"Columns in the report", "Curr Key", "Data Type"}, "FR_Year", "FR_Column_Name"),{{"FR_Year", type number}}), each ([Curr Key] = null) and ([FR_Year] = FlashReportYear)),{"Curr Key", "Data Type", "FR_Year"}),{"FR_Column_Name", "Columns in the report"})))
in
Source
================================================================
* GetListOfStructureColumns
(FR_Year)=>
let
Source = Table.RemoveColumns(Table.SelectRows(Table.RemoveColumns(Table.SelectRows(Table.TransformColumnTypes(Table.UnpivotOtherColumns(Excel.CurrentWorkbook(){[Name="ColumnNames"]}[Content], {"Columns in the report", "Curr Key", "Data Type"}, "FR_Year", "FR_Column_Name"),{{"FR_Year", type number}}), each ([Curr Key] = null)),{"Curr Key", "Data Type", "FR_Column_Name"}), each ([FR_Year] = FR_Year)),{"FR_Year"})[Columns in the report]
in
Source
================================================================
* RenameColumns
(Source, Headers)=>
let
#"Renamed Columns" = Table.RenameColumns(Source,Headers,MissingField.Ignore)
in
#"Renamed Columns"
================================================================
* Extract_Months
(String) =>
let
MyKeywords = {" 01", " 02", " 03", " 04", " 05", " 06", " 07", " 08", " 09", " 10", " 11", " 12"},
MatchFound = List.Transform(List.Buffer(MyKeywords), each Text.Contains(String, _, Comparer.OrdinalIgnoreCase)),
//index position of match found
Position = List.PositionOf(MatchFound, true),
//return null if Position is negative
Return = if Position < 0 then null else MyKeywords{Position}
in
Return
================================================================
|
