Forum Discussion
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 |
| D | aar |
| A | arr |
| B | thhtrjh |
| C | sf |
| D | sgkuy |
| E | sdfz |
| A | scsff |
| C | sretj |
| D | ki |
to the following table
| A | B | C | D | E | |
| record 1 | regerge | ere | et | aar | |
| record 2 | arr | thhtrjh | sf | sgkuy | sdfz |
| record 3 | scsff | sretj | ki |
It maybe looks easy for an expert but surely not for me...
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
13 Replies
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?
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
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
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"
