let
Source = Folder.Files("C:\Users"),
#"Filtered Rows" = Table.SelectRows(Source, let earliest = List.Min(Source[Date modified]) in each [Date modified] = earliest),
#"Filtered Hidden Files1" = Table.SelectRows(#"Filtered Rows", each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File (7)", each #"Transform File (7)"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File (7)"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File (7)", Table.ColumnNames(#"Transform File (7)"(#"Sample File (7)"))),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Column1", type any}, {"Best Flexible Rate - My Hotels LOS 1, Booking.com", type text}, {"Column3", type any}, {"Column4", type number}, {"Column5", type any}, {"Column6", type number}, {"Column7", type any}, {"Column8", type number}, {"Column9", type any}, {"Column10", type number}, {"Column11", type any}, {"Column12", type number}, {"Column13", type any}, {"Column14", type number}, {"Column15", type any}, {"Column16", type number}, {"Column17", type any}, {"Column18", type number}, {"Column19", type any}, {"Column20", type number}, {"Column21", type any}, {"Column22", type number}, {"Column23", type any}, {"Column24", type number}, {"Column25", type any}, {"Column26", type number}, {"Column27", type any}, {"Column28", type number}, {"Column29", type any}, {"Column30", type number}, {"Column31", type any}, {"Column32", type number}, {"Column33", type any}, {"Column34", type number}, {"Column35", type any}, {"Column36", type number}, {"Column37", type any}, {"Column38", type number}, {"Column39", type any}, {"Column40", type number}, {"Column41", type any}, {"Column42", type number}, {"Column43", type any}, {"Column44", type number}, {"Column45", type any}, {"Column46", type number}, {"Column47", type any}, {"Column48", type number}, {"Column49", type any}, {"Column50", type number}, {"Column51", type any}, {"Column52", type number}, {"Column53", type any}, {"Column54", type number}, {"Column55", type any}, {"Column56", type number}, {"Column57", type any}, {"Column58", type number}, {"Column59", type any}, {"Column60", type number}, {"Column61", type any}, {"Column62", type number}, {"Column63", type any}, {"Column64", type number}, {"Column65", type any}, {"Column66", type number}, {"Column67", type any}, {"Column68", type number}, {"Column69", type any}, {"Column70", type number}, {"Column71", type any}, {"Column72", type number}, {"Column73", type any}, {"Column74", type number}, {"Column75", type any}, {"Column76", type number}, {"Column77", type any}, {"Column78", type number}, {"Column79", type any}, {"Column80", type number}, {"Column81", type any}, {"Column82", type number}, {"Column83", type any}, {"Column84", type number}, {"Column85", type any}, {"Column86", type number}, {"Column87", type any}, {"Column88", type number}, {"Column89", type any}, {"Column90", type number}, {"Column91", type any}, {"Column92", type number}, {"Column93", type any}, {"Column94", type number}, {"Column95", type any}, {"Column96", type number}, {"Column97", type any}, {"Column98", type number}, {"Column99", type any}, {"Column100", type number}, {"Column101", type any}, {"Column102", type number}, {"Column103", type any}, {"Column104", type number}, {"Column105", type any}, {"Column106", type number}, {"Column107", type any}, {"Column108", type number}, {"Column109", type any}, {"Column110", type number}, {"Column111", type any}, {"Column112", type number}, {"Column113", type any}, {"Column114", type number}, {"Column115", type any}, {"Column116", type number}, {"Column117", type any}, {"Column118", type number}, {"Column119", type any}, {"Column120", type number}, {"Column121", type any}, {"Column122", type number}, {"Column123", type any}, {"Column124", type number}, {"Column125", type any}, {"Column126", type number}, {"Column127", type any}, {"Column128", type number}, {"Column129", type any}, {"Column130", type number}, {"Column131", type any}, {"Column132", type number}, {"Column133", type any}, {"Column134", type number}, {"Column135", type any}, {"Column136", type number}, {"Column137", type any}, {"Column138", type number}, {"Column139", type any}, {"Column140", type number}, {"Column141", type any}, {"Column142", type number}, {"Column143", type any}, {"Column144", type number}, {"Column145", type any}, {"Column146", type number}, {"Column147", type any}, {"Column148", type number}, {"Column149", type any}, {"Column150", type number}, {"Column151", type any}, {"Column152", type number}, {"Column153", type any}, {"Column154", type number}, {"Column155", type any}, {"Column156", type number}, {"Column157", type any}, {"Column158", type number}, {"Column159", type any}, {"Column160", type number}, {"Column161", type any}, {"Column162", type number}, {"Column163", type any}, {"Column164", type number}, {"Column165", type any}, {"Column166", type number}, {"Column167", type any}, {"Column168", type number}, {"Column169", type any}, {"Column170", type number}, {"Column171", type any}, {"Column172", type number}, {"Column173", type any}, {"Column174", type number}, {"Column175", type any}, {"Column176", type number}, {"Column177", type any}, {"Column178", type number}, {"Column179", type any}, {"Column180", type number}, {"Column181", type any}, {"Column182", type number}, {"Column183", type any}, {"Column184", type number}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Source.Name", "Column1"}),
#"Merged Queries" = Table.NestedJoin(#"Removed Columns", {"Best Flexible Rate - My Hotels LOS 1"}, Portfolio, {"OTA Insights"}, "Portfolio", JoinKind.LeftOuter),
#"Expanded Portfolio_AMH" = Table.ExpandTableColumn(#"Merged Queries", "Portfolio", {"Hotel Code", "Ideas Name"}, {"Hotel Code", "Ideas Name"}),
#"Reordered Columns" = Table.ReorderColumns(#"Expanded Portfolio",{"Hotel Code", "Ideas Name", "Best Flexible Rate - My Hotels LOS 1", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17", "Column18", "Column19", "Column20", "Column21", "Column22", "Column23", "Column24", "Column25", "Column26", "Column27", "Column28", "Column29", "Column30", "Column31", "Column32", "Column33", "Column34", "Column35", "Column36", "Column37", "Column38", "Column39", "Column40", "Column41", "Column42", "Column43", "Column44", "Column45", "Column46", "Column47", "Column48", "Column49", "Column50", "Column51", "Column52", "Column53", "Column54", "Column55", "Column56", "Column57", "Column58", "Column59", "Column60", "Column61", "Column62", "Column63", "Column64", "Column65", "Column66", "Column67", "Column68", "Column69", "Column70", "Column71", "Column72", "Column73", "Column74", "Column75", "Column76", "Column77", "Column78", "Column79", "Column80", "Column81", "Column82", "Column83", "Column84", "Column85", "Column86", "Column87", "Column88", "Column89", "Column90", "Column91", "Column92", "Column93", "Column94", "Column95", "Column96", "Column97", "Column98", "Column99", "Column100", "Column101", "Column102", "Column103", "Column104", "Column105", "Column106", "Column107", "Column108", "Column109", "Column110", "Column111", "Column112", "Column113", "Column114", "Column115", "Column116", "Column117", "Column118", "Column119", "Column120", "Column121", "Column122", "Column123", "Column124", "Column125", "Column126", "Column127", "Column128", "Column129", "Column130", "Column131", "Column132", "Column133", "Column134", "Column135", "Column136", "Column137", "Column138", "Column139", "Column140", "Column141", "Column142", "Column143", "Column144", "Column145", "Column146", "Column147", "Column148", "Column149", "Column150", "Column151", "Column152", "Column153", "Column154", "Column155", "Column156", "Column157", "Column158", "Column159", "Column160", "Column161", "Column162", "Column163", "Column164", "Column165", "Column166", "Column167", "Column168", "Column169", "Column170", "Column171", "Column172", "Column173", "Column174", "Column175", "Column176", "Column177", "Column178", "Column179", "Column180", "Column181", "Column182", "Column183", "Column184"}),
#"Filtered Rows1" = Table.SelectRows(#"Reordered Columns", each ([Column3] <> null)),
#"Promoted Headers" = Table.PromoteHeaders(#"Filtered Rows1", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Tue 07 Jan 2020", type any}, {"Column5", type number}, {"Wed 08 Jan 2020", type any}, {"Column7", type number}, {"Thu 09 Jan 2020", type any}, {"Column9", type number}, {"Fri 10 Jan 2020", type any}, {"Column11", type number}, {"Sat 11 Jan 2020", type any}, {"Column13", type number}, {"Sun 12 Jan 2020", type any}, {"Column15", type number}, {"Mon 13 Jan 2020", type any}, {"Column17", type number}, {"Tue 14 Jan 2020", type any}, {"Column19", type number}, {"Wed 15 Jan 2020", type any}, {"Column21", type number}, {"Thu 16 Jan 2020", type any}, {"Column23", type number}, {"Fri 17 Jan 2020", type any}, {"Column25", type number}, {"Sat 18 Jan 2020", type any}, {"Column27", type number}, {"Sun 19 Jan 2020", type any}, {"Column29", type number}, {"Mon 20 Jan 2020", type any}, {"Column31", type number}, {"Tue 21 Jan 2020", type any}, {"Column33", type number}, {"Wed 22 Jan 2020", type any}, {"Column35", type number}, {"Thu 23 Jan 2020", type any}, {"Column37", type number}, {"Fri 24 Jan 2020", type any}, {"Column39", type number}, {"Sat 25 Jan 2020", type any}, {"Column41", type number}, {"Sun 26 Jan 2020", type any}, {"Column43", type number}, {"Mon 27 Jan 2020", type any}, {"Column45", type number}, {"Tue 28 Jan 2020", type any}, {"Column47", type number}, {"Wed 29 Jan 2020", type any}, {"Column49", type number}, {"Thu 30 Jan 2020", type any}, {"Column51", type number}, {"Fri 31 Jan 2020", type any}, {"Column53", type number}, {"Sat 01 Feb 2020", type any}, {"Column55", type number}, {"Sun 02 Feb 2020", type any}, {"Column57", type number}, {"Mon 03 Feb 2020", type any}, {"Column59", type number}, {"Tue 04 Feb 2020", type any}, {"Column61", type number}, {"Wed 05 Feb 2020", type any}, {"Column63", type number}, {"Thu 06 Feb 2020", type any}, {"Column65", type number}, {"Fri 07 Feb 2020", type any}, {"Column67", type number}, {"Sat 08 Feb 2020", type any}, {"Column69", type number}, {"Sun 09 Feb 2020", type any}, {"Column71", type number}, {"Mon 10 Feb 2020", type any}, {"Column73", type number}, {"Tue 11 Feb 2020", type any}, {"Column75", type number}, {"Wed 12 Feb 2020", type any}, {"Column77", type number}, {"Thu 13 Feb 2020", type any}, {"Column79", type number}, {"Fri 14 Feb 2020", type any}, {"Column81", type number}, {"Sat 15 Feb 2020", type any}, {"Column83", type number}, {"Sun 16 Feb 2020", type any}, {"Column85", type number}, {"Mon 17 Feb 2020", type any}, {"Column87", type number}, {"Tue 18 Feb 2020", type any}, {"Column89", type number}, {"Wed 19 Feb 2020", type any}, {"Column91", type number}, {"Thu 20 Feb 2020", type any}, {"Column93", type number}, {"Fri 21 Feb 2020", type any}, {"Column95", type number}, {"Sat 22 Feb 2020", type any}, {"Column97", type number}, {"Sun 23 Feb 2020", type any}, {"Column99", type number}, {"Mon 24 Feb 2020", type any}, {"Column101", type number}, {"Tue 25 Feb 2020", type any}, {"Column103", type number}, {"Wed 26 Feb 2020", type any}, {"Column105", type number}, {"Thu 27 Feb 2020", type any}, {"Column107", type number}, {"Fri 28 Feb 2020", type any}, {"Column109", type number}, {"Sat 29 Feb 2020", type any}, {"Column111", type number}, {"Sun 01 Mar 2020", type any}, {"Column113", type number}, {"Mon 02 Mar 2020", type any}, {"Column115", type number}, {"Tue 03 Mar 2020", type any}, {"Column117", type number}, {"Wed 04 Mar 2020", type any}, {"Column119", type number}, {"Thu 05 Mar 2020", type any}, {"Column121", type number}, {"Fri 06 Mar 2020", type any}, {"Column123", type number}, {"Sat 07 Mar 2020", type any}, {"Column125", type number}, {"Sun 08 Mar 2020", type any}, {"Column127", type number}, {"Mon 09 Mar 2020", type any}, {"Column129", type number}, {"Tue 10 Mar 2020", type any}, {"Column131", type number}, {"Wed 11 Mar 2020", type any}, {"Column133", type number}, {"Thu 12 Mar 2020", type any}, {"Column135", type number}, {"Fri 13 Mar 2020", type any}, {"Column137", type number}, {"Sat 14 Mar 2020", type any}, {"Column139", type number}, {"Sun 15 Mar 2020", type any}, {"Column141", type number}, {"Mon 16 Mar 2020", type any}, {"Column143", type number}, {"Tue 17 Mar 2020", type any}, {"Column145", type number}, {"Wed 18 Mar 2020", type any}, {"Column147", type number}, {"Thu 19 Mar 2020", type any}, {"Column149", type number}, {"Fri 20 Mar 2020", type any}, {"Column151", type number}, {"Sat 21 Mar 2020", type any}, {"Column153", type number}, {"Sun 22 Mar 2020", type any}, {"Column155", type number}, {"Mon 23 Mar 2020", type any}, {"Column157", type number}, {"Tue 24 Mar 2020", type any}, {"Column159", type number}, {"Wed 25 Mar 2020", type any}, {"Column161", type number}, {"Thu 26 Mar 2020", type any}, {"Column163", type number}, {"Fri 27 Mar 2020", type any}, {"Column165", type number}, {"Sat 28 Mar 2020", type any}, {"Column167", type number}, {"Sun 29 Mar 2020", type any}, {"Column169", type number}, {"Mon 30 Mar 2020", type any}, {"Column171", type number}, {"Tue 31 Mar 2020", type any}, {"Column173", type number}, {"Wed 01 Apr 2020", type any}, {"Column175", type number}, {"Thu 02 Apr 2020", type any}, {"Column177", type number}, {"Fri 03 Apr 2020", type any}, {"Column179", type number}, {"Sat 04 Apr 2020", type any}, {"Column181", type number}, {"Sun 05 Apr 2020", type any}, {"Column183", type number}, {"Mon 06 Apr 2020", type any}, {"Column185", type number}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Column1", "Hotel Code"}, {"Column2", "Hotel Name"}}),
#"Filtered Rows2" = Table.SelectRows(#"Renamed Columns", each ([Hotel Name] <> null)),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Filtered Rows2", {"Hotel Code", "Hotel Name", "Column3"}, "Attribute", "Value"),
#"Removed Columns1" = Table.RemoveColumns(#"Unpivoted Columns",{"Column3", "Hotel Code"}),
#"Added Custom" = Table.AddColumn(#"Removed Columns1", "Percent", each if Text.Contains([Attribute], "Column") then [Value] else null),
#"Filled Up" = Table.FillUp(#"Added Custom",{"Percent"}),
#"Filtered Rows3" = Table.SelectRows(#"Filled Up", each not Text.Contains([Attribute], "Column")),
#"Split Column by Delimiter" = Table.SplitColumn(#"Filtered Rows3", "Attribute", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Attribute.1", "Attribute.2", "Attribute.3", "Attribute.4"}),
#"Merged Queries1" = Table.NestedJoin(#"Split Column by Delimiter", {"Attribute.3"}, Months, {"MonthAccronym"}, "Months", JoinKind.LeftOuter),
#"Expanded Months" = Table.ExpandTableColumn(#"Merged Queries1", "Months", {"Month#"}, {"Month#"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Expanded Months",{{"Month#", type text}}),
#"Added Custom1" = Table.AddColumn(#"Changed Type2", "Date", each Text.Combine({[Attribute.2],[#"Month#"],[Attribute.4]},"/")),
#"Changed Type3" = Table.TransformColumnTypes(#"Added Custom1",{{"Date", type date}}),
#"Removed Columns2" = Table.RemoveColumns(#"Changed Type3",{"Month#", "Attribute.1", "Attribute.2", "Attribute.3", "Attribute.4"}),
#"Reordered Columns1" = Table.ReorderColumns(#"Removed Columns2",{"Hotel Name", "Date", "Value", "Percent"}),
#"Changed Type4" = Table.TransformColumnTypes(#"Reordered Columns1",{{"Percent", Percentage.Type}})
in
#"Changed Type4"