UliPlabst
commented
2 years ago Hi, sorry for the late response, I was on holiday.
The issue with your query is that you use a single digit as part of an identifier. I mark the issues with a red apple 🍎 in the in the snippet below.
let
Source = Table.NestedJoin(ZVMC_D03_C001, {"Material"}, #"MDM MARA", {"MATNR"}, "MDM MARA", JoinKind.LeftOuter),
Expanded_MDM_MARA = Table.ExpandTableColumn(Source, "MDM MARA", {"MATNR", "MATKL", "MEINS", "BRGEW", "NTGEW", "GEWEI"}, {"MDM MARA.MATNR", "MDM MARA.MATKL", "MDM MARA.MEINS", "MDM MARA.BRGEW", "MDM MARA.NTGEW", "MDM MARA.GEWEI"}),
Renamed_Columns = Table.RenameColumns(Expanded_MDM_MARA,{{"MDM MARA.BRGEW", "MARA Gross weight"}, {"MDM MARA.MEINS", "MARA Base UOM"}, {"MDM MARA.MATKL", "MARA Matl Group"}, {"MDM MARA.NTGEW", "MARA Net Weight"}, {"MDM MARA.MATNR", "MARA.Matl Number"}}),
Added_MARA_Gross_Wgt_Lbs = Table.AddColumn(Renamed_Columns, "MARA Gross Weight Lbs", each if [MDM MARA.GEWEI] = "KG" then [MARA Gross weight] * 2.20462 else [MARA Gross weight], type number),
Added_WeightLbs = Table.AddColumn(Added_MARA_Gross_Wgt_Lbs, "Weight in LBS", each if [Unit] = "KG" then [QtyinUnE] * 2.20462 else if [Unit] = "LB" then [QtyinUnE] else if [PO Unit] = "KG" then [QtyinOPUn] * 2.20462 else if [PO Unit] = "LB" then [QtyinOPUn] else if [Base UoM] = "KG" then [SKUQty] * 2.20462 else if [Base UoM] = "LB" then [SKUQty] else if [Base UoM] <> "KG" or [Base UoM] <> "LB" then [MARA Gross Weight Lbs] * [SKUQty] else 0, type number),
Renamed_MonthYear = Table.RenameColumns(Added_WeightLbs,{{"Calendar Year / Month", "Month / Year"}}),
Format_MonthYear = Table.TransformColumnTypes(Renamed_MonthYear,{{"Month / Year", type date}}),
Renamed_Columns2 = Table.RenameColumns(Format_MonthYear,{{"QtyinOPUn", "Qty in OPUn"}, {"Material Group Desc (M)", "Material Group Desc"}, {"Material Group (M)", "Material Group"}}),
Split_YearMonthKey = Table.SplitColumn(Renamed_Columns2, "Calendar Year/Month Key", Splitter.SplitTextByPositions({4}), {"Calendar Month"}),
Added_YearMonth🍎.1🍎 = Table.AddColumn(Split_YearMonthKey, "Year / Month", each [Calendar Year] & " - " & [Calendar Month], type text),
SortedRows_YearMonth = **Table.Sort(Added_YearMonth🍎.1🍎,{{"Year** / Month", Order.Ascending}}),
Added_ConsignmentKey = Table.AddColumn(SortedRows_YearMonth, "Consignment Key", each [#"Year / Month"]&[Material]&[Plant]&[Vendor], type text),
Merged_OPS_RKWA = Table.NestedJoin(Added_ConsignmentKey, {"Consignment Key"}, #"OPS RKWA", {"Consignment Key"}, "OPS RKWA", JoinKind.LeftOuter),
Expanded_OPS_RKWA = Table.ExpandTableColumn(Merged_OPS_RKWA, "OPS RKWA", {"Qty"}, {"OPS RKWA.Qty"}),
Added_WeightLbsConsign = Table.AddColumn(Expanded_OPS_RKWA, "Weight in Lbs Consign", each if [Weight in LBS] = null then [OPS RKWA.Qty] else [Weight in LBS], type number),
Added_WeightsT = Table.AddColumn(Added_WeightLbsConsign, "Weight in sT", each [Weight in Lbs Consign] / 2000, type number),
Removed_Columns = Table.RemoveColumns(Added_WeightsT,{"Calendar Year/Month Key.1", "Weight in LBS", "Consignment Key", "OPS RKWA.Qty"}),
Renamed_WeightinLBSConsign = Table.RenameColumns(Removed_Columns,{{"Weight in Lbs Consign", "Weight in LBS"}}),
Merged_DivisionBusinessUnit = Table.NestedJoin(Renamed_WeightinLBSConsign, {"Division (M)"}, #"Division_Business Unit", {"Title"}, "Division_Business Unit", JoinKind.LeftOuter),
#"Expanded Division_Business Unit" = Table.ExpandTableColumn(Merged_DivisionBusinessUnit, "Division_Business Unit", {"StrategicBU"}, {"HES.StrategicBU"}),
Merged_HPSProfitCenterBU = Table.NestedJoin(#"Expanded Division_Business Unit", {"Profit Center"}, #"HPS Profit Center_Business Unit", {"ProfitCenter"}, "HPS Profit Center_Business Unit", JoinKind.LeftOuter),
Expanded_HPSProfitCenterBU = Table.ExpandTableColumn(Merged_HPSProfitCenterBU, "HPS Profit Center_Business Unit", {"BusinessUnit"}, {"HUS.BusinessUnit"}),
Added_BusinessUnit = Table.AddColumn(Expanded_HPSProfitCenterBU, "BU", each if [HES.StrategicBU] <> null then [HES.StrategicBU] else if [HUS.BusinessUnit] <> null then [HUS.BusinessUnit] else [#"Division Desc (M)"], type text),
RenamedColumns3 = Table.RenameColumns(Added_BusinessUnit,{{"Hubbell Group", "Group"}}),
Added_HubbellGroup = Table.AddColumn(RenamedColumns3, "Hubbell Group", each if [BU] = "HGCA" then "HUS" else if [Group] = "HPS" then "HUS" else if [Group] = "HLI" then "HES" else if [Group] = "HCI" then "HES" else if [Group] = "HCE" then "HES" else if [Group] = "HGO" then "HGO" else if [Group] = "CORP" then "CORP" else "Unknown", type text),
RemovedColumns = Table.RemoveColumns(Added_HubbellGroup,{"Group", "HES.StrategicBU", "HUS.BusinessUnit"}),
#"Added_Month#" = Table.AddColumn(RemovedColumns, "Month #", each Date.Month([#"Month / Year"])),
#"Format_Month#" = Table.TransformColumnTypes(#"Added_Month#",{{"Month #", Int64.Type}}),
ReorderedColumns3 = Table.ReorderColumns(#"Format_Month#",{"MARA Matl Group", "MARA.Matl Number", "MARA Gross Weight Lbs", "MARA Gross weight", "MARA Net Weight", "MDM MARA.GEWEI", "MARA Base UOM", "Division (M)", "Division Desc (M)", "Profit Center", "SKUQty", "Base UoM", "Qty in OPUn", "PO Unit", "QtyinUnE", "Unit", "Doc Type", "Month #", "Month / Year", "Calendar Year", "Year / Month", "Hubbell Group", "BU", "Plant", "Plant Desc", "Vendor", "Vendor Desc", "Material Group", "Material Group Desc", "Material", "Material Desc", "Total Spend USD", "Weight in LBS", "Weight in sT"}),
Merged_IndexMatlGrpCategorization = Table.NestedJoin(ReorderedColumns3, {"Hubbell Group", "Material Group"}, Index_Matl_Grp_Categorization, {"Group", "Material Group (M)"}, "Index_Matl_Grp_Categorization", JoinKind.LeftOuter),
Expanded_IndexMatlGrpCat = Table.ExpandTableColumn(Merged_IndexMatlGrpCategorization, "Index_Matl_Grp_Categorization", {"Metal", "Uom"}, {"Metal", "Uom"}),
Format_Uom = Table.TransformColumnTypes(Expanded_IndexMatlGrpCat,{{"Uom", type text}}),
Merged_IndexValues = Table.NestedJoin(Format_Uom, {"Metal", "Month / Year"}, #"Index Values", {"Index Link to Spend", "Lag Month"}, "Index Values", JoinKind.LeftOuter),
Expanded_IndexValues = Table.ExpandTableColumn(Merged_IndexValues, "Index Values", {"Index Value"}, {"Index Value CY"}),
Added_PYMonth = Table.AddColumn(Expanded_IndexValues, "Prior Year Date", each Date.AddYears([#"Month / Year"], -1)),
Format_PYMonth = Table.TransformColumnTypes(Added_PYMonth,{{"Prior Year Date", type date}}),
Merged_IndexValues2 = Table.NestedJoin(Format_PYMonth, {"Metal", "Prior Year Date"}, #"Index Values", {"Index Link to Spend", "Lag Month"}, "Index Values", JoinKind.LeftOuter),
Expanded_IndexValues2 = Table.ExpandTableColumn(Merged_IndexValues2, "Index Values", {"Index Value"}, {"Index Values.Index Value"}),
#"Renamed _IndexValuePY" = Table.RenameColumns(Expanded_IndexValues2,{{"Index Values.Index Value", "Index Value PY"}}),
Format_IndexValuePY = Table.TransformColumnTypes(#"Renamed _IndexValuePY",{{"Index Value PY", type number}}),
#"Added_WeightsT/Lbs" = Table.AddColumn(Format_IndexValuePY, "Weight sT/Lbs", each if [Uom] = "sT" then [Weight in sT] else if [Uom] = "Lbs" then [Weight in LBS] else 0, type number),
#"Added_YoYImpact$" = Table.AddColumn(#"Added_WeightsT/Lbs", "YoY Impact $", each if [Metal] = "Steel - Scrap" then [#"Weight sT/Lbs"] * 0.2 *([Index Value CY] - [Index Value PY]) else
[#"Weight sT/Lbs"] * ([Index Value CY] - [Index Value PY])),
#"Format_YoYImpact$" = Table.TransformColumnTypes(#"Added_YoYImpact$",{{"YoY Impact $", type number}}),
Renamed_Division = Table.RenameColumns(#"Format_YoYImpact$",{{"Division (M)", "Division"}}),
Filtered_MonthYear = Table.SelectRows(Renamed_Division, each Date.IsInPreviousNMonths([#"Month / Year"], 48)),
Added_YTD = Table.AddColumn(Filtered_MonthYear, "YTD", each Date.IsInYearToDate([#"Month / Year"])),
Added_Outliers = Table.AddColumn(Added_YTD, "Outliers", each if [Total Spend USD] > 0 and [#"YoY Impact $"] > [Total Spend USD] + 50000 then "remove" else if [Total Spend USD] > 0 and [#"YoY Impact $"] < ([Total Spend USD] * -1) - 50000 then "remove" else "keep", type text),
ReplacedErrors = Table.ReplaceErrorValues(Added_Outliers, {{"Outliers", "keep"}}),
Filtered_Outliers = Table.SelectRows(ReplacedErrors, each [Outliers] = "keep"),
Merged_MDMKDP_VW_MBEW = Table.NestedJoin(Filtered_Outliers, {"Material", "Plant"}, #"MDM KDP_VW_MBEW", {"MATNR_Material", "BWKEY_Valuation_area"}, "MDM KDP_VW_MBEW", JoinKind.LeftOuter),
Expanded_MDMKDP_VW_MBEW = Table.ExpandTableColumn(Merged_MDMKDP_VW_MBEW, "MDM KDP_VW_MBEW", {"Standard Pce Price"}, {"Standard Pce Price"}),
Added_StandardCostCurrent = Table.AddColumn(Expanded_MDMKDP_VW_MBEW, "Standard Cost Current", each [Standard Pce Price] * [SKUQty], Currency.Type)
in
Added_StandardCostCurrent
So the reason why it cannot be formatted is that the code cannot be parsed. I use the official microsoft powerquery parser for this so the issue is not within my code. Now most languages do not allow identifiers that start with a digit so is this actually a bug? When we look at the relevant sections from the m language specification:
identifier:
regular-identifier
quoted-identifier
regular-identifier:
available-identifier
available-identifier dot-character regular-identifier
available-identifier:
A keyword-or-identifier that is not a keyword
keyword-or-identifier:
letter-character
underscore-character
identifier-start-character identifier-part-characters
identifier-start-character:
letter-character
underscore-character
identifier-part-characters:
identifier-part-character identifier-part-charactersopt
identifier-part-character:
letter-character
decimal-digit-character
underscore-character
connecting-character
combining-character
formatting-characterwe can clearly see that a regular-identifier consists of identifier-start-character followed by identifier-part-characters. But identifier-start-character does not allow digit-character so just like in other languages as I suspected a identifier that starts with a digit is invalid. Examples:
test.a = 10 is valid
test.1 = 10 is invalid
test._1 = 10 is valid (because identifier-start-character allows underscore-character)
So the bug is actually not in the powerquery-parser library that I use but in power bi itself. It allows execution of powerquery code that is not according to specification. I will file a bug report in the powerquery-parser repository to indicate this inconsistent behavior to microsoft.
Thanks for the report.
TLDR: Added_YearMonth.1 is not a valid identifier, use Added_YearMonth._1 or Added_YearMonth.first
Description Using the web formatter.
Getting this error for the below code.
Errors: Expected to find a equal operator <'='>, but a numeric literal was found instead at 10:1830
let Source = Table.NestedJoin(ZVMC_D03_C001, {"Material"}, #"MDM MARA", {"MATNR"}, "MDM MARA", JoinKind.LeftOuter), Expanded_MDM_MARA = Table.ExpandTableColumn(Source, "MDM MARA", {"MATNR", "MATKL", "MEINS", "BRGEW", "NTGEW", "GEWEI"}, {"MDM MARA.MATNR", "MDM MARA.MATKL", "MDM MARA.MEINS", "MDM MARA.BRGEW", "MDM MARA.NTGEW", "MDM MARA.GEWEI"}), Renamed_Columns = Table.RenameColumns(Expanded_MDM_MARA,{{"MDM MARA.BRGEW", "MARA Gross weight"}, {"MDM MARA.MEINS", "MARA Base UOM"}, {"MDM MARA.MATKL", "MARA Matl Group"}, {"MDM MARA.NTGEW", "MARA Net Weight"}, {"MDM MARA.MATNR", "MARA.Matl Number"}}), Added_MARA_Gross_Wgt_Lbs = Table.AddColumn(Renamed_Columns, "MARA Gross Weight Lbs", each if [MDM MARA.GEWEI] = "KG" then [MARA Gross weight] 2.20462 else [MARA Gross weight], type number), Added_WeightLbs = Table.AddColumn(Added_MARA_Gross_Wgt_Lbs, "Weight in LBS", each if [Unit] = "KG" then [QtyinUnE] 2.20462 else if [Unit] = "LB" then [QtyinUnE] else if [PO Unit] = "KG" then [QtyinOPUn] 2.20462 else if [PO Unit] = "LB" then [QtyinOPUn] else if [Base UoM] = "KG" then [SKUQty] 2.20462 else if [Base UoM] = "LB" then [SKUQty] else if [Base UoM] <> "KG" or [Base UoM] <> "LB" then [MARA Gross Weight Lbs] * [SKUQty] else 0, type number), Renamed_MonthYear = Table.RenameColumns(Added_WeightLbs,{{"Calendar Year / Month", "Month / Year"}}), Format_MonthYear = Table.TransformColumnTypes(Renamed_MonthYear,{{"Month / Year", type date}}), Renamed_Columns2 = Table.RenameColumns(Format_MonthYear,{{"QtyinOPUn", "Qty in OPUn"}, {"Material Group Desc (M)", "Material Group Desc"}, {"Material Group (M)", "Material Group"}}), Split_YearMonthKey = Table.SplitColumn(Renamed_Columns2, "Calendar Year/Month Key", Splitter.SplitTextByPositions({4}), {"Calendar Month"}), Added_YearMonth.1 = Table.AddColumn(Split_YearMonthKey, "Year / Month", each [Calendar Year] & " - " & [Calendar Month], type text), SortedRows_YearMonth = Table.Sort(Added_YearMonth.1,{{"Year / Month", Order.Ascending}}), Added_ConsignmentKey = Table.AddColumn(SortedRows_YearMonth, "Consignment Key", each [#"Year / Month"]&[Material]&[Plant]&[Vendor], type text), Merged_OPS_RKWA = Table.NestedJoin(Added_ConsignmentKey, {"Consignment Key"}, #"OPS RKWA", {"Consignment Key"}, "OPS RKWA", JoinKind.LeftOuter), Expanded_OPS_RKWA = Table.ExpandTableColumn(Merged_OPS_RKWA, "OPS RKWA", {"Qty"}, {"OPS RKWA.Qty"}), Added_WeightLbsConsign = Table.AddColumn(Expanded_OPS_RKWA, "Weight in Lbs Consign", each if [Weight in LBS] = null then [OPS RKWA.Qty] else [Weight in LBS], type number), Added_WeightsT = Table.AddColumn(Added_WeightLbsConsign, "Weight in sT", each [Weight in Lbs Consign] / 2000, type number), Removed_Columns = Table.RemoveColumns(Added_WeightsT,{"Calendar Year/Month Key.1", "Weight in LBS", "Consignment Key", "OPS RKWA.Qty"}), Renamed_WeightinLBSConsign = Table.RenameColumns(Removed_Columns,{{"Weight in Lbs Consign", "Weight in LBS"}}), Merged_DivisionBusinessUnit = Table.NestedJoin(Renamed_WeightinLBSConsign, {"Division (M)"}, #"Division_Business Unit", {"Title"}, "Division_Business Unit", JoinKind.LeftOuter),
"Expanded Division_Business Unit" = Table.ExpandTableColumn(Merged_DivisionBusinessUnit, "Division_Business Unit", {"StrategicBU"}, {"HES.StrategicBU"}),
[#"Weight sT/Lbs"] * ([Index Value CY] - [Index Value PY])),
"Format_YoYImpact$" = Table.TransformColumnTypes(#"Added_YoYImpact$",{{"YoY Impact $", type number}}),
in Added_StandardCostCurrent