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

Hi dufoq3  thanks for your all kind of help. I am kind stuck with new thing. Currently I have to a situation of data in which i have not just only max but the 5 top value of max "VOICE_TRAFFIC_3G_Erl" in 7 days of data with respect to utrancel and date. 
Let me explain you in detail.
1. We have tables of 7 days hourly base data. For each day we have separate csv file.
2. The query we require is we took data from all 7 days or any days provided tables and it give us table with respect to top 5 values of "VOICE_TRAFFIC_3G_Erl". Means no matter how many days data we provide it only took top 5 value data of "VOICE_TRAFFIC_3G_Erl" . Means it check all the values of each date with hour respect and give us back the max top 5 value of all the data provided for 7 days. And all other relevant column included with it.
3. Currently I am attaching 5 days hourly data but it is not an issue as the query will automatically select the top 5 values from all the data. So whether it will be 5 days or any number of days it just has to select 5 top value "VOICE_TRAFFIC_3G_Erl"  in all the data with respect to unique "Utrancell" column. Means the last table have for every Utrancell we have 5 entries of data which is the top 5 values of "VOICE_TRAFFIC_3G_Erl" and all other relevant columns included.

Link of Tables 

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