Hi – new to the forum and (fairly) new to Power Query.
I have managed to get the basics down (I think), and just started trying to tinker with the Advanced options.
What I have been trying to do is download a file, based on whether or not a value on aseparate table is True/False, if it is true it downloads one way, if falseanother file.
Like I said, I’m still trying to get my head around the advanced editor and how the codeis structured, but from what I understand you reference the previous variable andthe actual order is less important.
Anyway, it got me here:
let
Bool = fnGetParameter("CSV"),
Sourcexl =Excel.Workbook(File.Contents("L:\Accounts Payable\Month End-AP\Month EndReports\2019\05 May 19\Germany Retail\APE12A.xlsx"), null, true),
APE12A_Sheet =Sourcexl{[Item="APE12A",Kind="Sheet"]}[Data],
#"Promoted Headersxl" =Table.PromoteHeaders(APE12A_Sheet, [PromoteAllScalars=true]),
#"Changed Typexl" =Table.TransformColumnTypes(#"PromotedHeadersxl",{{"BREAKLVL", Int64.Type}, {"OVERFLOW",type text}, {"SUPN15", type text}, {"SNAM05", type text},{"PER", Int64.Type}, {"BBAL1501", type number}}),
#"Grouped Rowsxl" =Table.Group(#"Changed Typexl", {"SUPN15","SNAM05"}, {{"Value", each List.Sum([BBAL1501]), typenumber}}),
#"Filtered Rowsxl" =Table.SelectRows(#"Grouped Rowsxl", each ([SUPN15] <>" "))
Source =Csv.Document(File.Contents("L:\Accounts Payable\Month End-AP\Month EndReports\2019\05 May 19\NB UK\APE12A.CSV"), [Delimiter=",",Columns=6, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" =Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Changed Type" =Table.TransformColumnTypes(#"PromotedHeaders",{{"BREAKLVL", Int64.Type}, {"OVERFLOW", typetext}, {"SUPN15", type text}, {"SNAM05", type text},{"PER", Int64.Type}, {"BBAL1501", type number}}),
#"Trimmed Text" =Table.TransformColumns(#"Changed Type",{{"SUPN15",Text.Trim, type text}}),
#"Grouped Rows" =Table.Group(#"Trimmed Text", {"SUPN15","SNAM05"}, {{"Value", each List.Sum([BBAL1501]), typenumber}}),
#"Filtered Rows" =Table.SelectRows(#"Grouped Rows", each ([SUPN15] <>""))
#"Table Ready" = if Bool then#"Filtered Rows" else #"Filtered Rowsxl",
in
#"Table Ready"
And itstelling me “Token Comma expected”, and I have no idea why, when I click “ShowError” it points me to the
Source = Csv.
Any pointersto where I am going wrong would be hugely appreciated.
Thanks,
I have managed to get the basics down (I think), and just started trying to tinker with the Advanced options.
What I have been trying to do is download a file, based on whether or not a value on aseparate table is True/False, if it is true it downloads one way, if falseanother file.
Like I said, I’m still trying to get my head around the advanced editor and how the codeis structured, but from what I understand you reference the previous variable andthe actual order is less important.
Anyway, it got me here:
let
Bool = fnGetParameter("CSV"),
Sourcexl =Excel.Workbook(File.Contents("L:\Accounts Payable\Month End-AP\Month EndReports\2019\05 May 19\Germany Retail\APE12A.xlsx"), null, true),
APE12A_Sheet =Sourcexl{[Item="APE12A",Kind="Sheet"]}[Data],
#"Promoted Headersxl" =Table.PromoteHeaders(APE12A_Sheet, [PromoteAllScalars=true]),
#"Changed Typexl" =Table.TransformColumnTypes(#"PromotedHeadersxl",{{"BREAKLVL", Int64.Type}, {"OVERFLOW",type text}, {"SUPN15", type text}, {"SNAM05", type text},{"PER", Int64.Type}, {"BBAL1501", type number}}),
#"Grouped Rowsxl" =Table.Group(#"Changed Typexl", {"SUPN15","SNAM05"}, {{"Value", each List.Sum([BBAL1501]), typenumber}}),
#"Filtered Rowsxl" =Table.SelectRows(#"Grouped Rowsxl", each ([SUPN15] <>" "))
Source =Csv.Document(File.Contents("L:\Accounts Payable\Month End-AP\Month EndReports\2019\05 May 19\NB UK\APE12A.CSV"), [Delimiter=",",Columns=6, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" =Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Changed Type" =Table.TransformColumnTypes(#"PromotedHeaders",{{"BREAKLVL", Int64.Type}, {"OVERFLOW", typetext}, {"SUPN15", type text}, {"SNAM05", type text},{"PER", Int64.Type}, {"BBAL1501", type number}}),
#"Trimmed Text" =Table.TransformColumns(#"Changed Type",{{"SUPN15",Text.Trim, type text}}),
#"Grouped Rows" =Table.Group(#"Trimmed Text", {"SUPN15","SNAM05"}, {{"Value", each List.Sum([BBAL1501]), typenumber}}),
#"Filtered Rows" =Table.SelectRows(#"Grouped Rows", each ([SUPN15] <>""))
#"Table Ready" = if Bool then#"Filtered Rows" else #"Filtered Rowsxl",
in
#"Table Ready"
And itstelling me “Token Comma expected”, and I have no idea why, when I click “ShowError” it points me to the
Source = Csv.
Any pointersto where I am going wrong would be hugely appreciated.
Thanks,