Forum Discussion
Tabel tansformation - need help
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
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
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
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
