data transformation

[Anonymous]

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

[v-lili6-msft · Accepted 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

[Anonymous]

Hi, this would work if i have duplicates with ID but with diffrent dates?

[v-lili6-msft]

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

[Anonymous]

Hi @v-lili6-msft

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

[v-lili6-msft]

hi, @Anonymous

Share some sample data with this error.

Best Regards,

Lin

[Anonymous]

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"

[v-lili6-msft]

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

[Anonymous]

https://docs.google.com/spreadsheets/d/1k3zlkZ-IzZ2rhVrEY8Ui8FkKBfy6AcHbp3_SFnxMg98/edit?usp=sharing

I still don't have expected results with duplicates..

[v-lili6-msft]

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

[Anonymous]

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