Forum Discussion

SoulCuber's avatar
SoulCuber
Frequent Visitor
10 years ago
Solved

Applied steps are not displayed after using advanced editor on desktop

I am a beginning user. When I create a query on the desktop step-by-step, then the applied steps are listed one-by-one. However, after I use the advanced editor to create a new step, then the applied...
Need Help
  • Anonymous's avatar
    Anonymous
    10 years ago

    The only reason individual steps would not show from handwritten code is that you are not actually writing the parts of your query as valid individual steps. You've either successfully written a single step with multiple functioning parts, or you've written nonfunctional code. To give any detailed help beyond that would require actually seeing the query code in question.

Anonymous's avatar
Anonymous
Not applicable
10 years ago

The only reason individual steps would not show from handwritten code is that you are not actually writing the parts of your query as valid individual steps. You've either successfully written a single step with multiple functioning parts, or you've written nonfunctional code. To give any detailed help beyond that would require actually seeing the query code in question.

fbluemke's avatar
fbluemke
Icon for Advocate I rankAdvocate I
6 years ago

Hello,

 

I am having same issue but cannot find the error in the code (I am definetly NOT an expert. I added the last 7 lines starting with addedcustom1:

 

let
    Source = Sql.Database("xx.x.xx.xxx", "xxx_xxxx"),
    dbo_V_SALES_BY_PRODUCT_FISCAL_MANUAL = Source{[Schema="dbo",Item="V_SALES_BY_PRODUCT_FISCAL"]}[Data],
    #"Changed Type5" = Table.TransformColumnTypes(dbo_V_SALES_BY_PRODUCT_FISCAL_MANUAL,{{"YTDLY_RETURN_AMT", type number}, {"YTDLY_RETURN_QTY", type number}, {"YTD_RETURN_AMT", type number}, {"YTD_RETURN_QTY", type number}, {"QTDLY_RETURN_AMT", type number}, {"QTDLY_RETURN_QTY", type number}, {"QTD_RETURN_AMT", type number}, {"QTD_RETURN_QTY", type number}, {"MTDLY_RETURN_AMT", type number}, {"MTDLY_RETURN_QTY", type number}, {"MTD_RETURN_AMT", type number}, {"MTD_RETURN_QTY", type number}, {"LWLY_RETURN_AMT", type number}, {"LWLY_RETURN_QTY", type number}, {"LW_RETURN_AMT", type number}, {"LW_RETURN_QTY", type number}, {"YTDLY_SHIP_AMT", type number}, {"YTD_SHIP_AMT", type number}, {"QTDLY_SHIP_AMT", type number}, {"QTD_SHIP_AMT", type number}, {"MTDLY_SHIP_AMT", type number}, {"MTD_SHIP_AMT", type number}, {"LWLY_SHIP_AMT", type number}, {"LW_SHIP_AMT", type number}, {"YTDLY_SHIP_QTY", type number}, {"YTD_SHIP_QTY", type number}, {"QTDLY_SHIP_QTY", type number}, {"QTD_SHIP_QTY", type number}, {"MTDLY_SHIP_QTY", type number}, {"MTD_SHIP_QTY", type number}, {"LWLY_SHIP_QTY", type number}, {"LW_SHIP_QTY", type number}, {"YTDLY_SALE_AMT", type number}, {"YTDLY_SALE_QTY", type number}, {"YTD_SALE_AMT", type number}, {"YTD_SALE_QTY", type number}, {"QTDLY_SALE_AMT", type number}, {"QTDLY_SALE_QTY", type number}, {"QTD_SALE_AMT", type number}, {"QTD_SALE_QTY", type number}, {"MTDLY_SALE_AMT", type number}, {"MTDLY_SALE_QTY", type number}, {"MTD_SALE_AMT", type number}, {"MTD_SALE_QTY", type number}, {"LWLY_SALE_AMT", type number}, {"LWLY_SALE_QTY", type number}, {"LW_SALE_AMT", type number}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type5",null,0,Replacer.ReplaceValue,{"ItemCost"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Replaced Value",{{"YTDLY_NET_SHIP_QTY", Int64.Type}, {"QTDLY_NET_SHIP_QTY", Int64.Type}, {"MTDLY_NET_SHIP_QTY", Int64.Type}, {"LWLY_NET_SHIP_QTY", Int64.Type}, {"YTD_NET_SHIP_QTY", Int64.Type}, {"QTD_NET_SHIP_QTY", Int64.Type}, {"MTD_NET_SHIP_QTY", Int64.Type}, {"LW_NET_SHIP_QTY", Int64.Type}}),
    #"Added Custom8" = Table.AddColumn(#"Changed Type", "LW_NET_COST_SALES", each [ItemCost]*[LW_NET_SHIP_QTY]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom8",{{"LW_NET_COST_SALES", type number}}),
    #"Added Custom9" = Table.AddColumn(#"Changed Type1", "LYLY_NET_COST_SALES", each [ItemCost]*[LWLY_NET_SHIP_QTY]),
    #"Added Custom10" = Table.AddColumn(#"Added Custom9", "MTD_NET_COST_SALES", each [ItemCost]*[MTD_NET_SHIP_QTY]),
    #"Added Custom11" = Table.AddColumn(#"Added Custom10", "MTDLY_NET_COST_SALES", each [MTDLY_NET_SHIP_QTY]*[ItemCost]),
    #"Added Custom12" = Table.AddColumn(#"Added Custom11", "QTD_NET_COST_SALES", each [QTD_NET_SHIP_QTY]*[ItemCost]),
    #"Added Custom13" = Table.AddColumn(#"Added Custom12", "QTDLY_NET_COST_SALES", each [ItemCost]*[QTDLY_NET_SHIP_QTY]),
    #"Added Custom14" = Table.AddColumn(#"Added Custom13", "YTD_NET_COST_SALES", each [YTD_NET_SHIP_QTY]*[ItemCost]),
    #"Added Custom15" = Table.AddColumn(#"Added Custom14", "YTDLY_NET_COST_SALES", each [YTDLY_NET_SHIP_QTY]*[ItemCost]),
    #"Changed Type2" = Table.TransformColumnTypes(#"Added Custom15",{{"YTDLY_NET_COST_SALES", Int64.Type}, {"YTD_NET_COST_SALES", type number}, {"QTDLY_NET_COST_SALES", Int64.Type}, {"QTD_NET_COST_SALES", type number}, {"MTDLY_NET_COST_SALES", Int64.Type}, {"MTD_NET_COST_SALES", Int64.Type}, {"LYLY_NET_COST_SALES", Int64.Type}}),
    #"Added Custom24" = Table.AddColumn(#"Changed Type2", "OH_COST_AMT", each [OnHandQty]*[ItemCost]),
    #"Added Custom25" = Table.AddColumn(#"Added Custom24", "OH_RETAIL_VALUE", each [OnHandQty]*[Price]),
    #"Changed Type3" = Table.TransformColumnTypes(#"Added Custom25",{{"OH_COST_AMT", type number}, {"YTDLY_NET_SHIP_AMT", type number}, {"YTD_NET_SHIP_AMT", type number}, {"QTDLY_NET_SHIP_AMT", type number}, {"QTD_NET_SHIP_AMT", type number}, {"MTDLY_NET_SHIP_AMT", type number}, {"MTD_NET_SHIP_AMT", type number}, {"LWLY_NET_SHIP_AMT", type number}, {"LW_NET_SHIP_AMT", type number}, {"OH_RETAIL_VALUE", type number}}),
    #"Added Custom26" = Table.AddColumn(#"Changed Type3", "LW_NET_MARGIN", each [LW_NET_SHIP_AMT]-[LW_NET_COST_SALES]),
    #"Renamed Columns" = Table.RenameColumns(#"Added Custom26",{{"LYLY_NET_COST_SALES", "LWLY_NET_COST_SALES"}}),
    #"Added Custom27" = Table.AddColumn(#"Renamed Columns", "LWLY_NET_MARGIN", each [LWLY_NET_SHIP_AMT]-[LWLY_NET_COST_SALES]),
    #"Added Custom28" = Table.AddColumn(#"Added Custom27", "MTD_NET_MARGIN", each [MTD_NET_SHIP_AMT]-[MTD_NET_COST_SALES]),
    #"Added Custom29" = Table.AddColumn(#"Added Custom28", "MTDLY_NET_MARGIN", each [MTDLY_NET_SHIP_AMT]-[MTDLY_NET_COST_SALES]),
    #"Added Custom30" = Table.AddColumn(#"Added Custom29", "QTD_NET_MARGIN", each [QTD_NET_SHIP_AMT]-[QTD_NET_COST_SALES]),
    #"Added Custom31" = Table.AddColumn(#"Added Custom30", "QTDLY_NET_MARGIN", each [QTDLY_NET_SHIP_AMT]-[QTDLY_NET_COST_SALES]),
    #"Added Custom32" = Table.AddColumn(#"Added Custom31", "YTD_NET_MARGIN", each [YTD_NET_SHIP_AMT]-[YTD_NET_COST_SALES]),
    #"Added Custom33" = Table.AddColumn(#"Added Custom32", "YTDLY_NET_MARGIN", each [YTDLY_NET_SHIP_AMT]-[YTDLY_NET_COST_SALES]),
    #"Changed Type4" = Table.TransformColumnTypes(#"Added Custom33",{{"YTDLY_NET_MARGIN", type number}, {"YTD_NET_MARGIN", type number}, {"QTDLY_NET_MARGIN", type number}, {"QTD_NET_MARGIN", type number}, {"MTDLY_NET_MARGIN", type number}, {"MTD_NET_MARGIN", type number}, {"LWLY_NET_MARGIN", type number}, {"LW_NET_MARGIN", type number}, {"YTDLY_NET_COST_SALES", type number}, {"QTDLY_NET_COST_SALES", type number}, {"MTDLY_NET_COST_SALES", type number}, {"MTD_NET_COST_SALES", type number}, {"LWLY_NET_COST_SALES", type number}, {"YTDLY_RETURN_AMT", type number}, {"YTD_RETURN_AMT", type number}, {"QTDLY_RETURN_AMT", type number}, {"QTD_RETURN_AMT", type number}, {"MTDLY_RETURN_AMT", type number}, {"MTD_RETURN_AMT", type number}, {"LWLY_RETURN_AMT", type number}, {"LW_RETURN_AMT", type number}, {"LWLY_SALE_AMT", type number}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type4", "LW_NET_DEMAND_MARGIN", each [LW_NET_DEMAND_AMT]-[LW_NET_DEMAND_COST_SALES]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "LWLY_NET_DEMAND_MARGIN", each [LWLY_NET_DEMAND_AMT]-[LWLY_NET_DEMAND_COST_SALES]),
    #"MTDGM" = Table.AddColumn(#"Added Custom1", "MTD_NET_DEMAND_MARGIN", each [MTD_NET_DEMAND_AMT]-[MTD_NET_DEMAND_COST_SALES]),
    #"MTDLYGM" = Table.AddColumn(#"MTDGM", "MTDLY_NET_DEMAND_MARGIN", each [MTDLY_NET_DEMAND_AMT]-[MTDLY_NET_DEMAND_COST_SALES]),
    #"QTDGM" = Table.AddColumn(#"MTDLYGM", "QTD_NET_DEMAND_MARGIN", each [QTD_NET_DEMAND_AMT]-[LWLY_NET_DEMAND_COST_SALES]),
    #"QTDLYGM" = Table.AddColumn(#"QTDGM", "QTDLY_NET_DEMAND_MARGIN", each [QTDLY_NET_DEMAND_AMT]-[QTDLY_NET_DEMAND_COST_SALES]),
    #"YTDGM" = Table.AddColumn(#"QTDLYGM", "YTD_NET_DEMAND_MARGIN", each [YTD_NET_DEMAND_AMT]-[YTD_NET_DEMAND_COST_SALES]),
    #"YTDLYGM" = Table.AddColumn(#"YTDGM", "YTDLY_NET_DEMAND_MARGIN", each [YTDLY_NET_DEMAND_AMT]-[YTDLY_NET_DEMAND_COST_SALES])

in
    #"Added Custom"

 

  • vhatp's avatar
    vhatp
    Icon for Advocate I rankAdvocate I
    1 year ago

    Hi, a late reply, but maybe someone else might benefit from reading it.
    The problem in your query is simple. You've manually added code for the last 7 steps and forgot to reference the last one in the last sentence. So the query just ignores your additions after all and finishes with step "Added Custom".
    Should be as follows:

    in
   #"YTDLYGM"

     

    • NJ_hokie's avatar
      NJ_hokie
      Regular Visitor
      1 year ago

      This did help me, I am new to m code and did not update what was after the 'in'. Glad you posted this years later, thanks!

    • xanna's avatar
      xanna
      Regular Visitor
      10 months ago

      Thank you for this! The other responses were not helpful at all. Yours was perfect & helped me solve my problem too. Thanks again.