Forum Discussion

aakmpp's avatar
aakmpp
Copper Contributor
Sep 12, 2023

I want to implement the following in power query which involves looping inside rows.

Any help is appreciated to solve the following:

 

I have generation data from nine engines stored in nine variables. say engine1, engine2 etc. Also, I have total demand for these engines. I want to distribute these demands according to the generations per engine. For that I have the following logic implemented. I want to implement the logic for each row.

But unfortunately, I'm unable to get it working properly.

 

To simply put it what I'm trying to achieve is,

  1. I want to take a number of fields.
  2. Then find the max value among those fields.
  3. Do some operation with it.
  4. Then expel that field which was max.
  5. And restart the process until my queue is empty.
  6. Then move to the next row.
  7. Thus complete the whole table.

 

let
    // Step 1: Load the "tbl" table
    Source = Excel.CurrentWorkbook(){[Name="G"]}[Content],
    //G_Table = Source{[Item="G",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Clock", type datetime}, {"E1", type number}, {"E2", type number}, {"E3", Int64.Type}, {"E4", type number}, {"E5", type number}, {"E6", type number}, {"E7", type number}, {"E8", type number}, {"E9", type number}, {"GrossGen", type number}, {"Active Energy Export MWh", type number}, {"Demand", type number}}),

    // Step 2: Define the list of engines explicitly
    Engines = {G_Table[E1], G_Table[E2], G_Table[E3], G_Table[E4], G_Table[E5], G_Table[E6], G_Table[E7], G_Table[E8], G_Table[E9]},


    // Step 3: Calculate Depandable Capacity (163/9)
    dc = 163 / 9,

    // Step 4: Calculate RemainingDemand
    RemainingDemand = G_Table[Demand],

    // Step 5: Loop through engines and allocate power
    AllocatePower = List.Generate(
        () => [i = 1, engineList = Engines],
        each [i] <= 9 and RemainingDemand > 0,
        each [
            i = [i] + 1,
            maxEngine = List.Max(engineList),
            allocatedPower = if maxEngine < RemainingDemand then maxEngine else dc,
            RemainingDemand = RemainingDemand - allocatedPower,
            engineList = List.RemoveFirstN(engineList, List.PositionOf(engineList, maxEngine))
        ],
        each [allocatedPower]
    ),

    // Combine the allocated power with the original table
    AllocatedTable = Table.AddColumn(G_Table, "AllocatedPower", each List.First(AllocatePower))
in
    AllocatedTable

 

 

BI & Data Analysis
excel
Formulas and Functions
power bi
  • LeonPavesic's avatar
    LeonPavesic
    Silver Contributor
    Sep 12, 2023

    Hi aakmpp,

    your Power Query Code is interesting and you're on the right track with it, but there are a few small issues in your code, such as how you define the list of engines and how you loop through them.

    I have tried to update (correct) the version of your Power Query code that should help to achieve your wishes:

    let
    // Step 1: Load the "tbl" table
    Source = Excel.CurrentWorkbook(){[Name="G"]}[Content],
    //"G_Table" was commented out, so I removed it.
    #"Changed Type" = Table.TransformColumnTypes(Source, {{"Clock", type datetime}, {"E1", type number}, {"E2", type number}, {"E3", Int64.Type}, {"E4", type number}, {"E5", type number}, {"E6", type number}, {"E7", type number}, {"E8", type number}, {"E9", type number}, {"GrossGen", type number}, {"Active Energy Export MWh", type number}, {"Demand", type number}}),

    // Step 2: Define the list of engines explicitly
    Engines = List.Transform({1..9}, each Text.From(_)), // Create a list of engine column names: {"E1", "E2", ..., "E9"}

    // Step 3: Calculate Dependable Capacity (163/9)
    dc = 163 / 9,

    // Step 4: Calculate Remaining Demand
    RemainingDemand = #"Changed Type"[Demand],

    // Step 5: Loop through engines and allocate power
    AllocatePower = List.Generate(
        () => [i = 1, engineList = Engines],
        each [i] <= 9 and RemainingDemand > 0,
        each [
            i = [i] + 1,
            maxEngine = List.Max(engineList),
            allocatedPower = if maxEngine < RemainingDemand then maxEngine else dc,
            RemainingDemand = RemainingDemand - allocatedPower,
            engineList = List.RemoveFirstN(engineList, List.PositionOf(engineList, maxEngine))
        ],
        each [allocatedPower]
    ),

    // Combine the allocated power with the original table
    AllocatedTable = Table.AddColumn(#"Changed Type", "AllocatedPower", each List.First(AllocatePower))
in
    AllocatedTable

    I created a list of engine column names using List.Transform so that you don't have to specify each engine manually and adjusted the table references to use "#Changed Type" where appropriate, as this is the step where you transformed your source data.


    Please click Mark as Best Response & Like if my post helped you to solve your issue.
    This will help others to find the correct solution easily. It also closes the item.


    If the post was useful in other ways, please consider giving it Like.


    Kindest regards,


    Leon Pavesic

    • aakmpp's avatar
      aakmpp
      Copper Contributor
      Sep 12, 2023

      LeonPavesic Thanks for your reply.

      But simply running it through ChatGPT won't help.

      At line 19 it is showing the following error:
      Expression.Error: The name 'engineList' wasn't recognized. Make sure it's spelled correctly.

Resources