Hello,
I was looking all over the place, but couldn't find a solution. However, I do believe that this has to be quite simple...
In my PQ table (F_BPOC), I have a text column called "Source.Name". This is a query that is getting the data from Excel files stored in a specific folder. Every week, I am adding a new file that will be called YYYYMMDD_BPOC.
This PQ is then loaded into the Excel report. What I want to have, is to filter PQ in a way that it will show only the most recent version of the file (i.e. "Source.Name" will be the highest). I was trying to extract only a number (text before delimiter "_") and get max value, but probably I have done something wrong... I am looking for a solution in the M language, as this is just PQ; I am not using PBI in here.
Below is my query from Advanced Editor. I know it's pretty ugly now, but I will clean it up after. 🙂
let
Source = Folder.Files("MyPath_BPOC"),
#"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File from BPOC", each #"Transform File from BPOC"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File from BPOC"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File from BPOC", Table.ColumnNames(#"Transform File from BPOC"(#"Sample File (2)"))),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Företag: SY - STANLEY Security Sverige AB", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type any}, {"Column6", type any}, {"Column7", type any}, {"Column8", type any}, {"Column9", type any}, {"Column10", type any}, {"Column11", type any}, {"Column12", type any}, {"Column13", type any}, {"Column14", type any}, {"Column15", type any}, {"Column16", type any}, {"Column17", type any}, {"Column18", type any}, {"Column19", type any}, {"Column20", type any}, {"Column21", type any}, {"Column22", type any}, {"Column23", type any}, {"Column24", type any}, {"Column25", type any}, {"Column26", type any}, {"Column27", type any}, {"Column28", type any}, {"Column29", type any}, {"Column30", type any}, {"Column31", type any}, {"Column32", type any}, {"Column33", type any}, {"Column34", type any}, {"Column35", type any}, {"Column36", type any}, {"Column37", type any}, {"Column38", type any}, {"Column39", type any}, {"Column40", type any}, {"Column41", type any}, {"Column42", type any}, {"Column43", type any}, {"Column44", type any}, {"Column45", type any}, {"Column46", type any}, {"Column47", type any}, {"Column48", type any}, {"Column49", type any}, {"Column50", type any}, {"Column51", type any}, {"Column52", type any}, {"Column53", type any}, {"Column54", type any}, {"Column55", type any}, {"Column56", type any}, {"Column57", type any}, {"Column58", type any}, {"Column59", type any}, {"Column60", type any}, {"Column61", type text}, {"Column62", type text}, {"Column63", type any}, {"Column64", type any}, {"Column65", type any}, {"Column66", type text}, {"Column67", type text}, {"Column68", type text}, {"Column69", type text}, {"Column70", type any}, {"Column71", type any}}),
#"Removed Top Rows" = Table.Skip(#"Changed Type",5),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"20200123_BPOC.xlsx", type text}, {"REGION", type text}, {"RESULTATENHET", type text}, {"KST", Int64.Type}, {"KST Benämning", type text}, {"PROJEKTNR", type text}, {"PROJEKTBENÄMNING", type text}, {"UTFALL", type number}, {"UTFALL_1", type number}, {"OBETALT inkl moms", Int64.Type}, {"OBETALT", type number}, {"KALKYL", Int64.Type}, {"KONTROLL", type number}, {"UTFALL_2", type number}, {"KALKYL_3", type number}, {"KONTROLL_4", type number}, {"UPPARBETNING", type number}, {"POC Intäkt", type number}, {"POC Kostnad", type number}, {"POC TB", type number}, {"POC TG%", type number}, {"POC Risk", type number}, {"UTFALL_5", type number}, {"KALKYL_6", type number}, {"UTFALL_7", type number}, {"KALKYL_8", type number}, {"UTFALL_9", type number}, {"KALKYL_10", type number}, {"KONTROLL_11", type number}, {"UTFALL_12", type number}, {"KALKYL_13", Int64.Type}, {"KONTROLL_14", type number}, {"UTFALL_15", type number}, {"KALKYL_16", Int64.Type}, {"KONTROLL_17", type number}, {"UTFALL_18", type number}, {"KALKYL_19", Int64.Type}, {"KONTROLL_20", type number}, {"UTFALL_21", Int64.Type}, {"KALKYL_22", Int64.Type}, {"UTFALL_23", type number}, {"KALKYL_24", Int64.Type}, {"UTFALL_25", type number}, {"KALKYL_26", Int64.Type}, {"UTFALL_27", type number}, {"KALKYL_28", Int64.Type}, {"UTFALL_29", type number}, {"KALKYL_30", Int64.Type}, {"UTFALL_31", Int64.Type}, {"KALKYL_32", Int64.Type}, {"UTFALL_33", type number}, {"KALKYL_34", Int64.Type}, {"BL Rev", type number}, {"BL TB", type number}, {"BL TG%", type number}, {"TOTAL WIP", type number}, {"WIP Matrl", type number}, {"WIP UE", type number}, {"WIP EA", type number}, {"VÅGEN", type number}, {"VÅGEN%", type any}, {"POC", type text}, {"ENTREPRENADFORM", type text}, {"ÖPPNINGSDATUM", Int64.Type}, {"PROGNOSMÅNAD", Int64.Type}, {"PL", Int64.Type}, {"PROJLEDARE, namn", type text}, {"SÄLJARE", type text}, {"SÄLJARE, namn", type text}, {"SÄLJKATEGORI", type text}, {"Check Utfall", Int64.Type}, {"Check Kalkyl", Int64.Type}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"20200123_BPOC.xlsx", "Source.Name"}}),
#"Merged Queries" = Table.NestedJoin(#"Renamed Columns",{"PROJEKTNR"},F_BETPLAN,{"Projekt"},"F_BETPLAN",JoinKind.LeftOuter),
#"Expanded F_BETPLAN" = Table.ExpandTableColumn(#"Merged Queries", "F_BETPLAN", {"Betplan", "Betplan(T)"}, {"Betplan", "Betplan(T)"}),
#"Merged Queries1" = Table.NestedJoin(#"Expanded F_BETPLAN",{"KST"},F_MAPPING,{"Kst"},"F_MAPPING",JoinKind.LeftOuter),
#"Expanded F_MAPPING" = Table.ExpandTableColumn(#"Merged Queries1", "F_MAPPING", {"Cluster"}, {"Cluster"}),
#"Renamed Columns2" = Table.RenameColumns(#"Expanded F_MAPPING",{{"UPPARBETNING", "POC %"}, {"UTFALL", "Order Intake"}, {"UTFALL_1", "Actual Billing"}}),
#"Removed Columns" = Table.RemoveColumns(#"Renamed Columns2",{"OBETALT inkl moms", "OBETALT"}),
#"Renamed Columns3" = Table.RenameColumns(#"Removed Columns",{{"KALKYL", "Revenue Budget"}}),
#"Removed Columns1" = Table.RemoveColumns(#"Renamed Columns3",{"KONTROLL"}),
#"Renamed Columns4" = Table.RenameColumns(#"Removed Columns1",{{"UTFALL_2", "Total Actual Cost"}, {"KALKYL_3", "Total Budgeted Cost"}}),
#"Removed Columns2" = Table.RemoveColumns(#"Renamed Columns4",{"KONTROLL_4"}),
#"Renamed Columns5" = Table.RenameColumns(#"Removed Columns2",{{"POC Intäkt", "POC Revenue Recognized"}}),
#"Changed Type2" = Table.TransformColumnTypes(#"Renamed Columns5",{{"POC %", Percentage.Type}}),
#"Renamed Columns6" = Table.RenameColumns(#"Changed Type2",{{"POC Kostnad", "POC Cost Recognized"}, {"POC TB", "POC GM Recognized"}, {"POC TG%", "POC GM % Recognized"}, {"UTFALL_5", "Actual GM"}, {"KALKYL_6", "Budgeted GM"}, {"UTFALL_7", "Actual GM%"}, {"KALKYL_8", "Budgeted GM%"}, {"UTFALL_9", "Actual Material Cost"}, {"KALKYL_10", "Budgeted Material Cost"}}),
#"Removed Columns3" = Table.RemoveColumns(#"Renamed Columns6",{"KONTROLL_11"}),
#"Renamed Columns7" = Table.RenameColumns(#"Removed Columns3",{{"UTFALL_12", "Actual Sub Con Cost"}, {"KALKYL_13", "Budgeted Sub Con Cost"}}),
#"Removed Columns4" = Table.RemoveColumns(#"Renamed Columns7",{"KONTROLL_14"}),
#"Renamed Columns8" = Table.RenameColumns(#"Removed Columns4",{{"UTFALL_15", "Actual Own Labor Cost"}, {"KALKYL_16", "Budgeted Own Labor Cost"}}),
#"Removed Columns5" = Table.RemoveColumns(#"Renamed Columns8",{"KONTROLL_17", "UTFALL_18", "KALKYL_19", "KONTROLL_20"}),
#"Renamed Columns9" = Table.RenameColumns(#"Removed Columns5",{{"UTFALL_23", "Installation Egen Actual"}, {"KALKYL_24", "Installation Egen Budget"}}),
#"Added Custom" = Table.AddColumn(#"Renamed Columns9", "A-conto %", each if [Actual Billing] = 0 or [Order Intake] = 0 then 0 else [Actual Billing]/[Order Intake]),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Mtrl%", each if [Budgeted Material Cost] = 0 or [Actual Material Cost] = 0 then 0 else [Actual Material Cost]/[Budgeted Material Cost]),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "Sub Con %", each if [Actual Sub Con Cost] = 0 or [Budgeted Sub Con Cost] = 0 then 0 else [Actual Sub Con Cost]/[Budgeted Sub Con Cost]),
#"Added Custom3" = Table.AddColumn(#"Added Custom2", "Ovr Eget", each [Actual Own Labor Cost]-[Installation Egen Actual]),
#"Added Custom4" = Table.AddColumn(#"Added Custom3", "Kalkyl Ovr Eget", each [Budgeted Own Labor Cost]-[Installation Egen Budget]),
#"Added Custom5" = Table.AddColumn(#"Added Custom4", "Ovr Eget %", each if [Ovr Eget] = 0 or [Kalkyl Ovr Eget] = 0 then 0 else [Ovr Eget]/[Kalkyl Ovr Eget]),
#"Added Custom6" = Table.AddColumn(#"Added Custom5", "Tekn%", each if [Installation Egen Actual] = 0 or [Installation Egen Budget] = 0 then 0 else [Installation Egen Actual]/[Installation Egen Budget]),
#"Changed Type3" = Table.TransformColumnTypes(#"Added Custom6",{{"A-conto %", type number}, {"Mtrl%", type number}, {"Sub Con %", type number}, {"Ovr Eget", type number}, {"Kalkyl Ovr Eget", type number}, {"Ovr Eget %", type number}, {"Tekn%", type number}}),
#"Removed Columns6" = Table.RemoveColumns(#"Changed Type3",{"POC Revenue Recognized", "POC Cost Recognized", "POC GM Recognized", "POC GM % Recognized", "Actual GM", "Budgeted GM", "Actual GM%", "Budgeted GM%", "UTFALL_21", "KALKYL_22", "UTFALL_25", "KALKYL_26", "UTFALL_27", "KALKYL_28", "UTFALL_29", "KALKYL_30", "UTFALL_31", "KALKYL_32", "UTFALL_33", "KALKYL_34", "BL Rev", "BL TB", "BL TG%", "TOTAL WIP", "WIP Matrl", "WIP UE", "WIP EA", "VÅGEN", "VÅGEN%", "ENTREPRENADFORM", "PL", "SÄLJARE", "SÄLJKATEGORI", "Check Utfall", "Check Kalkyl"}),
#"Capitalized Each Word" = Table.TransformColumns(#"Removed Columns6",{{"PROJLEDARE, namn", Text.Proper, type text}}),
#"Uppercased Text" = Table.TransformColumns(#"Capitalized Each Word",{{"PROJEKTBENÄMNING", Text.Upper, type text}}),
#"Changed Type4" = Table.TransformColumnTypes(#"Uppercased Text",{{"POC %", type number}, {"Order Intake", Int64.Type}, {"Actual Billing", Int64.Type}, {"Revenue Budget", Int64.Type}, {"Total Actual Cost", Int64.Type}, {"Total Budgeted Cost", Int64.Type}, {"POC Risk", Int64.Type}, {"Actual Material Cost", Int64.Type}, {"Budgeted Material Cost", Int64.Type}, {"Actual Sub Con Cost", Int64.Type}, {"Budgeted Sub Con Cost", Int64.Type}, {"Actual Own Labor Cost", Int64.Type}, {"Budgeted Own Labor Cost", Int64.Type}, {"Installation Egen Actual", Int64.Type}, {"Installation Egen Budget", Int64.Type}}),
#"Added Custom7" = Table.AddColumn(#"Changed Type4", "Delta POC-Aconto %", each [#"POC %"]-[#"A-conto %"]),
#"Changed Type5" = Table.TransformColumnTypes(#"Added Custom7",{{"Delta POC-Aconto %", type number}}),
#"Inserted Text Between Delimiters" = Table.AddColumn(#"Changed Type5", "Text Between Delimiters", each Text.BetweenDelimiters([Source.Name], "2020", "23"), type text),
#"Removed Columns7" = Table.RemoveColumns(#"Inserted Text Between Delimiters",{"Text Between Delimiters"}),
#"Inserted Text Before Delimiter" = Table.AddColumn(#"Removed Columns7", "Text Before Delimiter", each Text.BeforeDelimiter([Source.Name], "_"), type text),
#"Changed Type6" = Table.TransformColumnTypes(#"Inserted Text Before Delimiter",{{"Text Before Delimiter", type date}}),
#"Duplicated Column" = Table.DuplicateColumn(#"Changed Type6", "Text Before Delimiter", "Text Before Delimiter - Copy"),
#"Extracted Month Name" = Table.TransformColumns(#"Duplicated Column", {{"Text Before Delimiter - Copy", each Date.MonthName(_), type text}}),
#"Duplicated Column1" = Table.DuplicateColumn(#"Extracted Month Name", "Text Before Delimiter", "Text Before Delimiter - Copy.1"),
#"Extracted Year" = Table.TransformColumns(#"Duplicated Column1",{{"Text Before Delimiter - Copy.1", Date.Year, Int64.Type}}),
#"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Extracted Year", {{"Text Before Delimiter - Copy.1", type text}}, "en-US"),{"Text Before Delimiter - Copy", "Text Before Delimiter - Copy.1"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged")
in
#"Merged Columns"
thanks a lot!
Marek
