Tabel tansformation - need help

[n8schicht]

Hello everybody,

have a table with article numbers and description texts. An article number can have several descriptive texts.

For this I have this table:

Artikel:	Text:
XY1	XY1-Text1
XY1	XY1-Text2
XY1	XY1-Text3
XY1	XY1-Text4
XY2	XY2-Text1
XY2	XY2-Text2
XY3	XY3-Text1
XY3	XY3-Text2
XY3	XY3-Text3
XY3	XY3-Text4
XY3	XY3-Text5
XY3	XY3-Text6
XY4	XY4-Text1
XY4	XY4-Text2
XY4	XY4-Text3
XY4	XY4-Text4

But I would like to transform it as follows:

Artikel:	Text1	Text2	Text3	Text4	Text5	Text6
XY1	XY1-Text1	XY1-Text2	XY1-Text3	XY1-Text4
XY2	XY2-Text1	XY2-Text2
XY3	XY3-Text1	XY3-Text2	XY3-Text3	XY3-Text4	XY3-Text5	XY3-Text6
XY4	XY4-Text1	XY4-Text2	XY4-Text3	XY4-Text4

Unfortunately I can't find a solution ... or I have no idea how to solve it. I think first of all the maximum number of texts per article number must be counted, then these columns must be generated and then all data must be in the respective correct column or line ...

[v-yiruan-msft · Accepted solution]

Hi @n8schicht ,

You can copy and paste the below codes in your Advanced Editor to get it:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wiog0VNIBkbohqRUlhkqxOuhiRljEjLGImUDFjMBiRijmIYvBzDMGixmjqEMWw6bOGIuYCRYxUyxiZlAxE7CYCYq9yGJGWMSMsYgB7Y0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Artikel = _t, Text = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Artikel", type text}, {"Text", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Artikel"}, {{"Count", each Table.AddIndexColumn(_, "Cum",1,1), type table}}),
    #"Expanded Table" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"Text", "Cum"}, {"Text", "Cum"}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Expanded Table", {{"Cum", type text}}, "en-US"), 
                        List.Distinct(Table.TransformColumnTypes(#"Expanded Table", {{"Cum", type text}}, "en-US")[Cum]), "Cum", "Text")
in  #"Pivoted Column"

Best Regards

[amitchandak]

@n8schicht , create a column in power query witch same as text

text1 = text

split the column by delimiter - https://www.tutorialgateway.org/how-to-split-columns-in-power-bi/

keep part 2

and then you can unpivot - https://radacad.com/pivot-and-unpivot-with-power-bi

or use that column of matrix

[n8schicht]

sorry, but i can´t understand... 

i don´t want to split the text...

i want to transform the values from article number from many rows to one row.... as i showed

[n8schicht]

@amitchandak 

so it looks in my Power-Query:

and this is how it should look after editing in Power-Bi:

[v-yiruan-msft · Accepted solution]

Hi @n8schicht ,

You can copy and paste the below codes in your Advanced Editor to get it:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wiog0VNIBkbohqRUlhkqxOuhiRljEjLGImUDFjMBiRijmIYvBzDMGixmjqEMWw6bOGIuYCRYxUyxiZlAxE7CYCYq9yGJGWMSMsYgB7Y0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Artikel = _t, Text = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Artikel", type text}, {"Text", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Artikel"}, {{"Count", each Table.AddIndexColumn(_, "Cum",1,1), type table}}),
    #"Expanded Table" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"Text", "Cum"}, {"Text", "Cum"}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Expanded Table", {{"Cum", type text}}, "en-US"), 
                        List.Distinct(Table.TransformColumnTypes(#"Expanded Table", {{"Cum", type text}}, "en-US")[Cum]), "Cum", "Text")
in  #"Pivoted Column"

Best Regards

[n8schicht]

@v-yiruan-msft 

Perfect! this is what i want to have! THX 

I still have one question instead.
Is there a way to automatically name the dynamically created number columns?
I just want to have a "Feature_" in front of the number.

I did it manually after creation, but that is not a good way, because it can be that there are more than the 8 columns mentioned at the moment in another data record ...

    #"Umbenannte Spalten" = Table.RenameColumns(#"Pivoted Column",{{"1", "Feature_1"}, {"2", "Feature_2"}, {"3", "Feature_3"}, {"4", "Feature_4"}, {"5", "Feature_5"}, {"6", "Feature_6"}, {"7", "Feature_7"}, {"8", "Feature_8"}})

---

[v-yiruan-msft · Accepted solution]

Hi @n8schicht ,

You can update the codes as below to achieve it:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wiog0VNIBkbohqRUlhkqxOuhiRljEjLGImUDFjMBiRijmIYvBzDMGixmjqEMWw6bOGIuYCRYxUyxiZlAxE7CYCYq9yGJGWMSMsYgB7Y0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Artikel = _t, Text = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Artikel", type text}, {"Text", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Artikel"}, {{"Count", each Table.AddIndexColumn(_, "Cum",1,1), type table}}),
#"Expanded Table" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"Text", "Cum"}, {"Text", "Cum"}),
#"Inserted Prefix" = Table.AddColumn(#"Expanded Table", "Prefix", each "Feature_" & Text.From([Cum], "en-US"), type text),
#"Removed Columns" = Table.RemoveColumns(#"Inserted Prefix",{"Cum"}),
#"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Removed Columns", {{"Prefix", type text}}, "en-US"),
List.Distinct(Table.TransformColumnTypes(#"Removed Columns", {{"Prefix", type text}}, "en-US")[Prefix]), "Prefix", "Text")
in
#"Pivoted Column"

Best Regards