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

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.