Forum Discussion

Thoughtknotseer's avatar
Thoughtknotseer
Regular Visitor
3 years ago
Solved

SelectRows apply to same column cause cyclic reference

Hi   I have a table. ID Exam Score 1 A 50 2 A 60 3 A 40 4 B 30 5 B 20 6 B 40   Then I hope to add to prepare like below column for using post analysis. ...
Need Help
  • m_alireza's avatar
    m_alireza
    3 years ago

    Hi Thoughtknotseer ,

    You can use the Group by Function in Power Query to achieve your desired result. 

    Copy the query below in advanced editor to see steps: 

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIEYlMDpVidaCUjKNcMwjWGck0gXBMg0wmIjSFcUyjXCMI1g3JBimMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Exam = _t, Score = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Exam", type text}, {"Score", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Exam"}, {{"MaxScore", each List.Max([Score]), type nullable number}, {"AllRows", each _, type table [ID=nullable number, Exam=nullable text, Score=nullable number]}}),
    #"Expanded AllRows" = Table.ExpandTableColumn(#"Grouped Rows", "AllRows", {"ID", "Score"}, {"ID", "Score"})
in
    #"Expanded AllRows"
m_alireza's avatar
m_alireza
Solution Specialist
3 years ago

Hi Thoughtknotseer ,

You can use the Group by Function in Power Query to achieve your desired result. 

Copy the query below in advanced editor to see steps: 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIEYlMDpVidaCUjKNcMwjWGck0gXBMg0wmIjSFcUyjXCMI1g3JBimMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Exam = _t, Score = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Exam", type text}, {"Score", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Exam"}, {{"MaxScore", each List.Max([Score]), type nullable number}, {"AllRows", each _, type table [ID=nullable number, Exam=nullable text, Score=nullable number]}}),
    #"Expanded AllRows" = Table.ExpandTableColumn(#"Grouped Rows", "AllRows", {"ID", "Score"}, {"ID", "Score"})
in
    #"Expanded AllRows"
  • Dwisha_S's avatar
    Dwisha_S
    Regular Visitor
    2 years ago

    Hi, m_alireza ,

    I have similar issue and using Table.Group function and still facing issue.

     

    I am looking to get the Average of forecast coloumn for Current Week to 6th Week Group by InternalID and I have tried multiple attempts to format the function.

    1) 
    let MINDATE = Date.WeekOfYear(DateTime.LocalNow()), Maxdate = MINDATE + 5, Source = #"Demand Fcst Raw Outputs", FilteredTable = Table.Group( Table.SelectRows(Source, each [Week Number] >= MINDATE and [Week Number] <= Maxdate), {"Internal ID"}, {{"TotalForecast", each List.Sum([Forecast]), type number}} ), FinalTable = Table.AddColumn(FilteredTable, "Test", each [TotalForecast] / 6) in FinalTable

    2) let
    FilteredTable = Table.Group(#"Demand Fcst Raw Outputs (2)", [Internal ID],List.Sum[Forecast],[Week Number]=Date.WeekOfYear(DateTime.LocalNow()) and [Week Number]<=Date.WeekOfYear(DateTime.LocalNow())+5),
    FinalTable = Table.AddColumn(FilteredTable, "Test", each [TotalForecast] / 6)
    in
    FinalTable