Data challenge (probably DAX)

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"