Forum Discussion
Helper IIextract the data with respect to few columns
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
Helper IIYes, but the data set i provided you need to extract their all other column value of the same row of maxium rrc column
- dufoq3
Community Champion
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- Sohaib
Ok 1 last thing if i want to select source as a folder not as CSV or Xlxs file what whould be the change in code
- dufoq3
Load your excel document from folder as new query. Paste here that your code and also screenshot of imported table.
- Sohaib
Hey 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
Hi, 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}})