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 IIOk 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
Community ChampionLoad 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"- dufoq3
Have you read my whole request?
- 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"