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
Community ChampionOk, 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
let
Source = Folder.Files("C:\Users\dell\Documents\Working\Data Validation\KPI Calculations 28-2-2024\KPI Calculation new method working\Data For Busy Hour Tables"),
#"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File (3)", each #"Transform File (3)"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File (3)"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File (3)", Table.ColumnNames(#"Transform File (3)"(#"Sample File (3)"))),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Hourly Table[EutranCells]", type text}, {"Hourly Table[Date New]", type datetime}, {"Hourly Table[EnodeB new]", type text}, {"Hourly Table[Hour]", Int64.Type}, {"Hourly Table[Begin Time New]", type datetime}, {"Hourly Table[End Time New]", type datetime}, {"Hourly Table[Granularity]", type text}, {"Hourly Table[Availability_4G]", type number}, {"Hourly Table[DL_THP_CELL_4G_Mbps]", type number}, {"Hourly Table[DL_PRB_Usage]", type number}, {"Hourly Table[DL_THP_USER_4G_Mbps]", type number}, {"Hourly Table[DL_THP_USER_QCI1_4G_Mbps]", Int64.Type}, {"Hourly Table[DL_THP_USER_QCI2_4G_Mbps]", Int64.Type}, {"Hourly Table[DL_THP_USER_QCI3_4G_Mbps]", Int64.Type}, {"Hourly Table[DL_THP_USER_QCI4_4G_Mbps]", Int64.Type}, {"Hourly Table[DL_THP_USER_QCI5_4G_Mbps]", Int64.Type}, {"Hourly Table[DL_THP_USER_QCI6_4G_Mbps]", type number}, {"Hourly Table[DL_THP_USER_QCI7_4G_Mbps]", type number}, {"Hourly Table[DL_THP_USER_QCI8_4G_Mbps]", Int64.Type}, {"Hourly Table[DL_THP_USER_QCI9_4G_Mbps]", Int64.Type}, {"Hourly Table[DL_VOL_4G_GB]", type number}, {"Hourly Table[ERAB Attempt]", Int64.Type}, {"Hourly Table[ERAB_DCR]", Int64.Type}, {"Hourly Table[ERAB_Drops]", Int64.Type}, {"Hourly Table[ERAB_SSR]", Int64.Type}, {"Hourly Table[ERAB_Failures]", Int64.Type}, {"Hourly Table[RRC_USERS]", type number}, {"Hourly Table[UL_THP_CELL_4G_Mbps]", type number}, {"Hourly Table[UL_PRB_Usage]", type number}, {"Hourly Table[UL_THP_USER_4G_Mbps]", type number}, {"Hourly Table[DL_Spectral_Eff]", type number}, {"Hourly Table[Avg_Agg_CQI]", type number}, {"Hourly Table[UL_VOL_4G_GB]", type number}, {"Hourly Table[VOLTE_SESSION_TIME_minutes]", Int64.Type}, {"Hourly Table[VOLTE_DCR]", Int64.Type}, {"Hourly Table[VOLTE_SSR]", Int64.Type}, {"Hourly Table[VoLTE_INTEGRITY_SESSION]", Int64.Type}, {"Hourly Table[VoLTE_INTEGRITY_UE]", Int64.Type}, {"Hourly Table[VOLTE_Erl]", Int64.Type}, {"Hourly Table[VOLTE_USERS]", type number}, {"Hourly Table[Reported Rank 1 %]", type any}, {"Hourly Table[Reported Rank 2 %]", type any}, {"Hourly Table[Reported Rank 3 %]", type any}, {"Hourly Table[Reported Rank 4 %]", type any}, {"Hourly Table[UL RSSI PUSCH New]", type any}, {"Hourly Table[UL RSSI PUCCH]", type any}, {"Hourly Table[QPSK Samples (%)]", type any}, {"Hourly Table[16 QAM Samples (%)]", type any}, {"Hourly Table[64QAM Samples(%)]", type any}, {"Hourly Table[RLC DL BLER]", type any}, {"Hourly Table[RLC UL BLER]", type any}, {"Hourly Table[UL SINR PUCCH]", type any}, {"Hourly Table[UL SINR PUSCH]", type any}, {"Hourly Table[PDCCH CFI Utilization]", type any}, {"Hourly Table[CCE Aggregation Level]", type any}})
in
#"Changed Type"
- 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}})