Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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 ...
Solved! Go to 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
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
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
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
110 | |
101 | |
97 | |
88 | |
68 |
User | Count |
---|---|
169 | |
133 | |
130 | |
103 | |
95 |