Data challenge (probably DAX)

[aabati68]

I am geeting data in PowerBI from an Excel file where I have 3 columns

I want to get to this result, where I have list of unique fruit name and overall count:

Many thanks for your help!

A,

[HotChilli]

Apart from adding the index (not needed - you can select all columns and Unpivot) , what's the difference between this and my response on Friday?

[aabati68]

Hi, the difference is that the answer that I accepted was more articulated and was describing the solution with more details. Your answer gave me the direction and the other gave me more details to do what I needed to do.

Thanks for your reply anyway, it was useful.

A.

[aabati68]

Thanks for the suggestion. I do agree, however the unpivot works well when there are no duplicated values in each column. While I do have many apples in the first columns etc...

[danextian · Accepted solution]

Hi @aabati68 ,

If you want a more complicated approach, you can try the first code below 🙂

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WciwoyElV0lFyzkgtKspMLQYyAzLzUhPBwrE6WBU4wiWdEvOAECTmnJOam5pXAtSKaQRWs3WU0EwAi8QCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Shop1 = _t, Shop2 = _t, Shop3 = _t]),
    Custom1 = 
        let
            PrevStep = Source,
            ColumnNames = Table.ColumnNames(PrevStep),
            Result = List.Combine(
                List.Transform(ColumnNames, (x) =>
                    let
                        t = Table.SelectColumns(PrevStep, {x}),
                        ColumnList = Table.ToList(t),
                        RemoveEmpty = List.Select ( ColumnList, each _ <> null and _ <> "" )
                    in
                        RemoveEmpty
                )
            ),
            ShopTable = Table.FromList(Result, Splitter.SplitByNothing(), {"Shop"})
        in
            ShopTable,
    #"Changed Type" = Table.TransformColumnTypes(Custom1,{{"Shop", type text}})

in
    #"Changed Type"

But this second one is much simpler and easier to understand. You just need to an index column or any other helper column. Select that column and unpivot all others. Remove the helper and Attribute columns, filter out the empty rows, change the data type and rename if necessary.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WciwoyElV0lFyzkgtKspMLQYyAzLzUhPBwrE6WBU4wiWdEvOAECTmnJOam5pXAtSKaQRWs3WU0EwAi8QCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Shop1 = _t, Shop2 = _t, Shop3 = _t]),
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Index"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute", "Index"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each [Value] <> null and [Value] <> "")
in
    #"Filtered Rows"

[aabati68]

Thanks a lot ! I went fot the second and easier solution and it worked !

A.

[HotChilli]

The original data format is not optimal.  I think you should Unpivot all columns in Power Query, so that you get

columns : shop, fruit

That will make it easier to get the final table visual (which would be fruit, count of fruit in a table)