Forum Discussion
How to transform excel text list to table
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
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
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:
=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
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"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
Hi Andris,
Working perfectly. Thanks for your support.
I learn something new & that's great.
My best regards
