Forum Discussion

Sohaib's avatar
Sohaib
Icon for Helper II rankHelper II
2 years ago
Solved

extract the data with respect to few columns

Hi I have these kind of data which i extract from the raw data set. I need to furthure extract it with respect to few columns   As you can see the above two mentioned columns for every ...
Need Help
  • dufoq3's avatar
    dufoq3
    2 years ago

    Ok, but keep in mind that for L_JCL014_I with date 17.3.2024 you have 2 rows with same RRC_USERS max value!

     

     

    let
    Source = Csv.Document(File.Contents("c:\Address\Hourly Table 20240404 192948.csv"),[Delimiter=",", Columns=55, Encoding=1250, QuoteStyle=QuoteStyle.None]),
    PromotedHeaders = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    TransformHeaders = Table.TransformColumnNames(PromotedHeaders, each Text.Trim(Text.BetweenDelimiters(_, "Hourly Table[", "]"))),
    ChangedType = Table.TransformColumns(TransformHeaders,{{"Date New", each Date.From(DateTime.From(_, "en-US")), type date}, {"RRC_USERS", each Number.From(_, "en-US"), type number}}),
    GroupedRows = Table.Group(ChangedType, {"EutranCells", "Date New"}, {{"Max RRC_USERS Row", each Table.SelectRows(_, (x)=> x[RRC_USERS] = List.Max([RRC_USERS])), type table}}),
    CombinedMaxRRC_USERS_Rows = Table.Combine(GroupedRows[Max RRC_USERS Row])
in
    CombinedMaxRRC_USERS_Rows

     

dufoq3's avatar
dufoq3
Icon for Community Champion rankCommunity Champion
2 years ago

Hi, we would like to help you, but provide sample data in usable format (not a screenshot) and expected result based on sample data please.

    • dufoq3's avatar
      dufoq3
      Icon for Community Champion rankCommunity Champion
      2 years ago

      Something like this?

       

      Result:

      let
    Source = Csv.Document(File.Contents("C:\Users\AddressToDocument\Hourly Table 20240404 192948.csv"),[Delimiter=",", Columns=55, Encoding=1250, QuoteStyle=QuoteStyle.None]),
    PromotedHeaders = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    TransformHeaders = Table.TransformColumnNames(PromotedHeaders, each Text.Trim(Text.BetweenDelimiters(_, "Hourly Table[", "]"))),
    RemovedOtherColumns = Table.SelectColumns(TransformHeaders,{"EutranCells", "Date New", "RRC_USERS"}),
    ChangedType = Table.TransformColumns(RemovedOtherColumns,{{"Date New", each Date.From(DateTime.From(_, "en-US")), type date}, {"RRC_USERS", each Number.From(_, "en-US"), type number}}),
    GroupedRows = Table.Group(ChangedType, {"EutranCells", "Date New"}, {{"Max RRC_USERS", each List.Max([RRC_USERS]), type number}, {"Detail", each _, type table}})
in
    GroupedRows
      • Sohaib's avatar
        Sohaib
        Icon for Helper II rankHelper II
        2 years ago

        Hi dafuq i need your help again please help me. The same kind of working. Now i need data with respect to max rrc but on weekly basis and for other query need it for monthly basis. Means last time we did on day basis now i need to do it with max rrc per week or max rrc per month. Please respond..your urgent help would be very helpfull.

        Data set