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 "Eutrancells" unique value their is 0 to 23 "hour" row data for every "Date". I need to calculate the Max value of "RCC User" columns between the range of 0 to 24 hour for every "date" row for each Eutracells and only give the max value of "RRC user" column fo unique "EUtracells" and for each "date" value. Please help

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

     

24 Replies

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

    Hi, we would like to help you, but provide sample data in usable format (not a screenshot) and expected result based on sample data please.

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

        Something like this?

         

        Result:

        let
    Source = Csv.Document(File.Contents("C:\Users\AddressToDocument\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[", "]"))),
    RemovedOtherColumns = Table.SelectColumns(TransformHeaders,{"EutranCells", "Date New", "RRC_USERS"}),
    ChangedType = Table.TransformColumns(RemovedOtherColumns,{{"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", each List.Max([RRC_USERS]), type number}, {"Detail", each _, type table}})
in
    GroupedRows
  • Sohaib's avatar
    Sohaib
    Icon for Helper II rankHelper II
    2 years ago

    Yes, but the data set i provided you need to extract their all other column value of the same row of maxium rrc column

    • dufoq3's avatar
      dufoq3
      Icon for Community Champion rankCommunity Champion
      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

        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

         

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

    Hi dufoq3 , one other query also arise need this data with respect to weekly or monthly, Currently we evaluate it on day level. I will attached the data set. Please help.

    Data set 

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

    need two result one for weekly and one monthly

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

      Try this:

       

      Weekly:

      let
    Source = Folder.Files("c:\Downloads\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}}),
    Ad_YearWeek = Table.AddColumn(ChangedType, "YearWeek", each Date.Year([Date New])*100 +  Date.WeekOfYear([Date New]), Int64.Type),
    GroupedRows = Table.Group(Ad_YearWeek, {"EutranCells", "YearWeek"}, {{"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

       

      Monthly:

      let
    Source = Folder.Files("c:\Downloads\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}}),
    Ad_YearMonth = Table.AddColumn(ChangedType, "YearMonth", each Date.Year([Date New])*100 + Date.Month([Date New]), Int64.Type),
    GroupedRows = Table.Group(Ad_YearMonth, {"EutranCells", "YearMonth"}, {{"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

        thanks. But I also sort out this as well. In this way I learned a alot.

  • 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 

      • 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