Forum Discussion
Sohaib
Helper II
Helper IIextract 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 ...
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
SohaibHelper II
Helper IIHey dufoq3 how are you? I need to understand if entries appaer or the entries have same value like 0 we have multiple entries in the result how to avoid multiple entries if by chance max rrc users column have same value or we have 0 value, Because at 0 value there are multiple entries appear. I need to pick only one randomly if the value is for 2 entries have same result or in case 0 just 1 random entry should be given in answer
dufoq3
Community Champion
Community ChampionHi, if you want to pick only 1 row, you can add this in GroupedRows step:
= Table.Group(ChangedType, {"EutranCells", "Date New"}, {{"Max RRC_USERS Row", each Table.FirstN(Table.SelectRows(_, (x)=> x[RRC_USERS] = List.Max([RRC_USERS])), 1), type table}})