Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Murat62
Regular Visitor

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

 

ColumnText
Aregerge
Bere
Cet
Daar
Aarr
Bthhtrjh
Csf
Dsgkuy
Esdfz
Ascsff
Csretj
Dki

 

to the following table

 

 ABCDE
record 1regergeereetaar 
record 2arrthhtrjhsfsgkuysdfz
record 3scsff sretjki 

 

It maybe looks easy for an expert but surely not for me...

 

Regards

1 ACCEPTED SOLUTION

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:

pbicommhelp.PNG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

=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)

pbicommhelp2.PNG

 

 

 

 

 

 

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

 

Best regards,

Andris

View solution in original post

13 REPLIES 13
smpa01
Super User
Super User

let
    Source = Web.Page(Web.Contents("https://community.powerbi.com/t5/Desktop/How-to-transform-excel-text-list-to-table/td-p/288742")),
    Data0 = Source{0}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Data0,{{"Column1", type text}, {"Column2", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Column", type text}, {"Text", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type1", {"Column"}, {{"Count", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Count],"IX",1,1)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Count"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Column", "Text", "IX"}, {"Custom.Column", "Custom.Text", "Custom.IX"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Expanded Custom",{"Column"}),
    #"Added Prefix" = Table.TransformColumns(#"Removed Columns1", {{"Custom.IX", each "record" & Text.From(_, "en-US"), type text}}),
    #"Pivoted Column" = Table.Pivot(#"Added Prefix", List.Distinct(#"Added Prefix"[Custom.Column]), "Custom.Column", "Custom.Text")
in
    #"Pivoted Column"
Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
vanessafvg
Super User
Super User

@Murat62 are you working in imported mode?

 

in powerquery you can pivot the data, click on the first column and from the transform menu click pivot and then select how you would like to aggregate it looks like you need dont aggregate

 

the only thing is what defines record 1?

 

 





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




Thanks for your quick answer.

 

I already tried to pivot this excel text list after importing it with power BI desktop.

 

It did not work in a way i got only counting numbers and not text values as i would like

 

Capture.PNG

 

Regards

 

Hi @Murat62,

 

Do you have any more information of your source table? Such as more columns. It's hard to create a table like yours with DAX and M based on my knowledge. I have tried a lot. One simple problem is it's hard to identify which records are record1.

 

Best Regards!

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thanks for your answer

 

Source data is coming from the web page where i have a list of companies name, with address, city, phone,...

I copied & pasted it to an excel file and insert a column indicating name/category per row as "company name", "address", "city", "phone".

Nevertheless, from my knowledge, i agree with you it hard to transform this list into table. I did not find by myself any solution even if this issue or problem looks to me common one.

Logically speaking as an example every time we find an "A" category in "column" it should create a new record into the table. 

 

Best regards

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:

pbicommhelp.PNG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

=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)

pbicommhelp2.PNG

 

 

 

 

 

 

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

 

Best regards,

Andris

Hi Andris,

 

Working perfectly. Thanks for your support.

I learn something new & that's great.

 

My best regards

In Power Quey, you can:

buffer the table,

group on "Column", chosing operation "All Rows",

adjust the generated code to have an index column added to the nested tables,

expand the nested tables,

pivot the column "Column" with advanced option "Don't Aggregate" and

as a finishing touch you may add prefix "Record " to the record numbers.

 

let
    Source = Table.Buffer(ExcelTextList),
    #"Grouped Rows" = Table.Group(Source, {"Column"}, {{"AllRows", each Table.AddIndexColumn(_, "Record", 1, 1), type table}}),
    #"Expanded AllRows" = Table.ExpandTableColumn(#"Grouped Rows", "AllRows", {"Text ", "Record"}, {"Text ", "Record"}),
    #"Pivoted Column" = Table.Pivot(#"Expanded AllRows", List.Distinct(#"Expanded AllRows"[Column]), "Column", "Text "),
    #"Added Prefix" = Table.TransformColumns(#"Pivoted Column", {{"Record", each "Record " & Text.From(_, "en-US"), type text}})
in
    #"Added Prefix"

 

Specializing in Power Query Formula Language (M)

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).

 

pbcommhelp4.PNG

 

 


 Here's what @Murat62 showed in the original post,

 

 

pbicommhelp3.PNG

 

 

 

 

 

And that's what you get with your formula.

 

Regards,

Andris

@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"
Specializing in Power Query Formula Language (M)

@MarcelBeug

 

Thanks for your answer. It looks quite too high level for me.

Nevertheless, it gave the opportunity to discover & learn new functions as table.buffer in Power Query.

will need time to digest them...

 

My best regards

My code with explanatory comments:

 

let
//  The Source is buffered (i.e. cached in memory). In general List.Accumulates performs better on a buffered table.
    Source = Table.Buffer(ExcelTextList),

/*  The purpose of the Accumulation step is to create a list with record numbers, starting with 1 and increasing with 1 each time the "Column" value is less than or equal to the previous "Column" value.
    As this requires both the previous "Column" value and the previous record number, the list is accumulated with the "Column" and record values in 1 item, so a list of lists.
    With Column values A,B,C,D,A,B, the list starts with {{A,0}}; after the first iteration it is {{A,0},{A,1}} etcetera.
    The first argument supplied to List.Accumulate is the list with "Column" values: Source[Column]. List.Accumulate will iterate - or loop - over this list.
    The second argument is the initial value or {{A,0}}.
    The third argument is the actual accumulation function:
        r refers to the accumulation so far, starting with {{A,0}}. The function transforms the value of r with each iteration. 
        c refers to the current item in the list of column values, starting with A.
        So the first iteration takes r {{A,0}} and concatenates: {{A, 1}}: 
            List.Last(r) takes the last item of the accumulated list {A,0}, so:
                List.Last(r){0} is the A from this pair.
                List.Last(r){1} is the 0 from this pair. As the A from this pair is <= c (Value A), the second element of the new pair becomes 0 + 1.  
        The second iteration takes r {{A,0},{A,1}} and concatenates {B,1}, resulting in {{A,0},{A,1},{B,1}} etcetera.
    After the accumulation, the first item {A,0} is removed with List.Skip.
*/
    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}}})),

//  The list with list is converted to a table with 2 columns (default Column1 and Column2).    
    Tabled = Table.FromRows(Accumulation),

/*  Column2 of this table (the record numbers) must be added as a new column to the original table.
    Unfortunately, no function is available to do that.
    But we can use Table.FromColumns which will take a list of lists, where each inner list contains column values.
    So first the original table must be transformed to a list of lists, using Table.ToColumns.
    Tabled[Column2] is concatenated to this list of list and this ensemble is converted to a table with Table.FromColumns.
    The (optional) second argument of table columns, defines the columns of the result. Value.Type(Source) would give the column names and types of the Source table.
    As the new table has an additional column (the record numbers), in this formula a dummy column is added to Source with column name "Record" and type Int64.Type.
    So the Value.Type is taken from: Source with the added "Record" column.
*/
    Combined = Table.FromColumns(Table.ToColumns(Source)&{Tabled[Column2]}, Value.Type(Table.AddColumn(Source, "Record", each null, Int64.Type))),

//  The rest is straightforward.
    #"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"
Specializing in Power Query Formula Language (M)

@MarcelBeug,

 

Can you explain please how exactly the code works (until the pivot part), what does it exactly do? It's working to me, but I can't totally understand every step of the formulas.

 

Regards,

Andris

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.