Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
hello,
I'm new to PowerBI and I have one issue with my report
The thing is I'm trying to "transpose" table like in the picture http://ifotos.pl/z/qwehnnw
* the red category always has oldest date
* in second table i can have id duplicates
Solved! Go to Solution.
hi, @Anonymous
Based on my test, you could tr these step in Edit Queries:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTLUNzTUNzIwtASyi1JTlGJ14OJGMPGknNJUsIQRRMIYXQNU3ARZg056UWpqnlJsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, Date = _t, category = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Date", type date}, {"category", type text}}), #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "category", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"category.1", "category.2"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"category.1", type text}, {"category.2", type text}}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"ID", "Date"}, "Attribute", "Value"), #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"}), #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Value]), "Value", "Date") in #"Pivoted Column"
Result:
Best Regards,
Lin
hi, @Anonymous
Based on my test, you could tr these step in Edit Queries:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTLUNzTUNzIwtASyi1JTlGJ14OJGMPGknNJUsIQRRMIYXQNU3ARZg056UWpqnlJsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, Date = _t, category = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Date", type date}, {"category", type text}}), #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "category", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"category.1", "category.2"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"category.1", type text}, {"category.2", type text}}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"ID", "Date"}, "Attribute", "Value"), #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"}), #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Value]), "Value", "Date") in #"Pivoted Column"
Result:
Best Regards,
Lin
Hi, this would work if i have duplicates with ID but with diffrent dates?
hi, @Anonymous
Yes, it will work well, you could try it by steps in the Edit Queries.
and here is my pbix file, please try it.
Best Regards,
Lin
I have this error for some rows, can you help?
Expression.Error: There were too many elements in the enumeration to complete the operation.
Details:
List
hi, @Anonymous
Share some sample data with this error.
Best Regards,
Lin
This is my code, and data in excel https://docs.google.com/spreadsheets/d/1ugyIfdUkb22GXeFv2OKR_VeMM5X8pmhscjMruc8t8Lc/edit?usp=sharing
let Source = Mail, #"Filtered Rows" = Table.SelectRows(Source, each ([New_or_revisit] <> null) and ([case_type] = "CF")), #"Removed Columns2" = Table.RemoveColumns(#"Filtered Rows",{"Subject", "Folder Path", "Sender.Address", "Importance", "Index", "New_or_revisit", "case_type"}), #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Columns2", "Categories", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Categories.1", "Categories.2"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Categories.1", type text}, {"Categories.2", type text}}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"sCRM_ID", "DateTimeReceived.1"}, "Attribute", "Value"), #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"}), #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Value]), "Value", "DateTimeReceived.1"), #"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column",{"Data completed"}) in #"Removed Columns1"
hi, @Anonymous
Add a group index column for
let Source = Mail, #"Filtered Rows" = Table.SelectRows(Source, each ([New_or_revisit] <> null) and ([case_type] = "CF")), #"Removed Columns2" = Table.RemoveColumns(#"Filtered Rows",{"Subject", "Folder Path", "Sender.Address", "Importance", "Index", "New_or_revisit", "case_type"}), #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Columns2", "Categories", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Categories.1", "Categories.2"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Categories.1", type text}, {"Categories.2", type text}}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"sCRM_ID", "DateTimeReceived.1"}, "Attribute", "Value"), #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"}), Partition = Table.Group( #"Removed Columns" , {"sCRM_ID"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}),
#"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"DateTimeReceived.1", "Value", "Index"}, {"DateTimeReceived.1", "Value", "Index"}),
#"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Value]), "Value", "DateTimeReceived.1"), #"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column",{"Data completed"}) in #"Removed Columns1"
Best Regards,
Lin
https://docs.google.com/spreadsheets/d/1k3zlkZ-IzZ2rhVrEY8Ui8FkKBfy6AcHbp3_SFnxMg98/edit?usp=sharing
I still don't have expected results with duplicates..
hi, @Anonymous
I'm sorry for my carelessness, try this code:
let Source = Mail, #"Filtered Rows" = Table.SelectRows(Source, each ([New_or_revisit] <> null) and ([case_type] = "CF")), #"Removed Columns2" = Table.RemoveColumns(#"Filtered Rows",{"Subject", "Folder Path", "Sender.Address", "Importance", "Index", "New_or_revisit", "case_type"}), #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Columns2", "Categories", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Categories.1", "Categories.2"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Categories.1", type text}, {"Categories.2", type text}}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"sCRM_ID", "DateTimeReceived.1"}, "Attribute", "Value"), #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"}), Partition = Table.Group( #"Removed Columns" , {"sCRM_ID"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}), #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"DateTimeReceived.1", "Value", "Index"}, {"DateTimeReceived.1", "Value", "Index"}), #"Pivoted Column" = Table.Pivot(#"Expanded Partition", List.Distinct(#"Expanded Partition"[Value]), "Value", "DateTimeReceived.1"), #"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column",{"Data completed"}) in #"Removed Columns1"
Best Regards,
Lin
Hi,
this kind of work, but i have other row in the table i need to keep so it do not work in the way I want 😞 I will try to work more with this base and maybe I will come up with some solutions. Anyway your response was very helpful, thank you
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |