Forum Discussion

n8schicht's avatar
n8schicht
Icon for Helper I rankHelper I
5 years ago
Solved

Tabel tansformation - need help

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:
XY1XY1-Text1
XY1XY1-Text2
XY1XY1-Text3
XY1XY1-Text4
XY2XY2-Text1
XY2XY2-Text2
XY3XY3-Text1
XY3XY3-Text2
XY3XY3-Text3
XY3XY3-Text4
XY3XY3-Text5
XY3XY3-Text6
XY4XY4-Text1
XY4XY4-Text2
XY4XY4-Text3
XY4XY4-Text4

 

But I would like to transform it as follows:

 

Artikel:Text1Text2Text3Text4Text5Text6
XY1XY1-Text1XY1-Text2XY1-Text3XY1-Text4  
XY2XY2-Text1XY2-Text2    
XY3XY3-Text1XY3-Text2XY3-Text3XY3-Text4XY3-Text5XY3-Text6
XY4XY4-Text1XY4-Text2XY4-Text3XY4-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 ...

 

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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

  • Anonymous's avatar
    Anonymous
    5 years ago

    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

6 Replies