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
dufoq3
Community Champion
Community ChampionHi,
I recommend you delete the query of which you provided the code. Also delete connected group:
Create new blank query and paste there this code.
- in Source step, replace whole address with your FOLDER addres where you have your .csv files stored. This query filters only .csv files, so you can have more files with other extensions in same folder or subfolders, but if you have other .csv files in same folder or subfolders, either move them or add additiional filters at after Source step.
let
Source = Folder.Files("c:\Address\PowerQueryForum\Sohaib\"),
FilteredHiddenFiles = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
FilteredCsv = Table.SelectRows(FilteredHiddenFiles, each [Extension] = ".csv"),
BinaryToTable = Table.TransformColumns(FilteredCsv, {{"Content", each Table.PromoteHeaders(Csv.Document(_,[Delimiter=",", Encoding=1250, QuoteStyle=QuoteStyle.None])), type table}}),
CombinedTables = Table.Combine(BinaryToTable[Content]),
TransformHeaders = Table.TransformColumnNames(CombinedTables, 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
