Forum Discussion

Sohaib's avatar
Sohaib
Icon for Helper II rankHelper II
2 years ago
Solved

extract 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 ...
Need Help
  • dufoq3's avatar
    dufoq3
    2 years ago

    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's avatar
Sohaib
Icon for Helper II rankHelper II
2 years ago

dufoq3 waiting for your reply please help

dufoq3's avatar
dufoq3
Icon for Community Champion rankCommunity Champion
2 years ago

Hi, try this. Change folder address.

 

let
    Source = Folder.Files("c:\Downloads\PowerQueryForum\Sohaib\CS BH Tables\"),
    CsvToTable = Table.TransformColumns(Source, {{"Content", each Table.PromoteHeaders(Csv.Document(_)), type table}}),
    Combined = Table.Combine(CsvToTable[Content]),
    ChangedType = Table.TransformColumns(Combined, {{"Date New", each Date.From(Text.BeforeDelimiter(_, " ")), type date}, {"VOICE_TRAFFIC_3G_Erl", each Number.From(_, "en-US"), type number}}),
    GroupedRows = Table.Group(ChangedType, {"Utrancell"}, {{"Max5", each Table.MaxN(_, {"VOICE_TRAFFIC_3G_Erl"}, 5), type table}}),
    CombinedMax5 = Table.Combine(GroupedRows[Max5])
in
    CombinedMax5