Solved: Re: extract the data with respect to few columns - Page 2

Sohaib · ‎04-04-2024

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 "Eutrancells" unique value their is 0 to 23 "hour" row data for every "Date". I need to calculate the Max value of "RCC User" columns between the range of 0 to 24 hour for every "date" row for each Eutracells and only give the max value of "RRC user" column fo unique "EUtracells" and for each "date" value. Please help

dufoq3 · ‎04-04-2024

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

Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

View solution in original post

Sohaib · ‎04-04-2024

Please download the dataset

dufoq3 · ‎04-04-2024

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

Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

Sohaib · ‎04-27-2024

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