Forum Discussion
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: XY1 XY1-Text1 ...
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
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
so it looks in my Power-Query:
and this is how it should look after editing in Power-Bi:
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
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"}})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