Forum Discussion

Murat62's avatar
Murat62
Regular Visitor
8 years ago
Solved

How to transform excel text list to table

Hello every one,   Firstly thanks for your coming support.   As an example, i would like to transform in power query the excel text list below   Column Text A regerge B ere C et...
Need Help
  • andris_'s avatar
    andris_
    8 years ago

    Hi Murat62,

     

    I'm not sure if I get this straight, but based on what you had said, my understanding is the following:

    The category column (named Column) is recursive, and whenever the sequence starts from the beginning (with "A" value), the record number increases with 1. 

     

    So, based on it, I don't know if there's any solution in the Power Query for that matter, but I figured out a way in Excel: if you create a new column with the following formula, you'll get the record numbers:

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

    =IFERROR(IF(A1>A2;C1+1;C1);1)

     

    If you load this table into PBI, you can create the pivot table in the Power Query you wanted. (with Column From Examples or even in Excel with the concatenate formula you can get the "record 1", "record 2"... format easily)

     

     

     

     

     

     

    Let me know please if it solves your problem and whether it was helpful or not.

     

    Best regards,

    Andris

andris_'s avatar
andris_
Icon for Resolver I rankResolver I
8 years ago

Hi MarcelBeug,

 

Your method is lovely, but according to my understanding, this - way because of the Group By - it gets the record numbers by the characters (so the first "A" will get 1, the second "A" get 2... the first "E" get 1), instead of the alphabetical order of the "sequences" (so the first sequence of 4 types "A, B, C, D" should get the record number 1, and after the second sequence of 5 "A, B, C, D, E" should get the record number 2).

 

 

 


 Here's what Murat62 showed in the original post,

 

 

 

 

 

 

 

And that's what you get with your formula.

 

Regards,

Andris

MarcelBeug's avatar
MarcelBeug
Icon for Community Champion rankCommunity Champion
8 years ago

andris_ Oops, you're right.

 

Your Excel solution, translated to Power Query, could look like:

 

let
    Source = Table.Buffer(ExcelTextList),
    Accumulation = List.Skip(List.Accumulate(Source[Column],{{Source[Column]{0},0}},(r,c) => r & {{c, if List.Last(r){0} >= c then List.Last(r){1} + 1 else List.Last(r){1}}})),
    Tabled = Table.FromRows(Accumulation),
    Combined = Table.FromColumns(Table.ToColumns(Source)&{Tabled[Column2]}, Value.Type(Table.AddColumn(Source, "Record", each null, Int64.Type))),
    #"Pivoted Column" = Table.Pivot(Combined, List.Distinct(Combined[Column]), "Column", "Text "),
    #"Added Prefix" = Table.TransformColumns(#"Pivoted Column", {{"Record", each "Record " & Text.From(_, "en-US"), type text}})
in
    #"Added Prefix"