Multiple column from a single column

[Anonymous]

I want to create table visual with multiple column from a single field

Eg: from the below table 

I want distict value from day field for each status in Multiple columns.

table visual should looks like below

Please help. Thanks in Advance

[amitchandak]

@Anonymous , Add an index column in power query

Power Query- Index Column: https://youtu.be/NS4esnCDqVw

Create this new column in DAX

Row = countx(filter(Table, [Status] = earlier([Status]) && [Index] <= earlier([Index]) ) , [Index])

Create a Matrix, Use Row on Row , Status on Column and Max of day as value

[Anonymous]

Its working but still have a problem, 

It showing repeated values in each column, need to display distinct values only.

And i couldnt find Max of Day, there are first,last,count,count(distict) options only

[Anonymous · Accepted solution]

Hi @Anonymous,

You can use the following calculated table expression to group your table records into different category columns based on 'status' and 'day' fields:

NewTable = 
FILTER (
    SELECTCOLUMNS (
        GENERATESERIES ( 1, 7, 1 ),
        "Index", [Value],
        "Full",
            LOOKUPVALUE (
                'Table'[day],
                'Table'[Status], "Full",
                'Table'[day], FORMAT ( [Value], "dddd" ),
                BLANK ()
            ),
        "Half",
            LOOKUPVALUE (
                'Table'[day],
                'Table'[Status], "Half",
                'Table'[day], FORMAT ( [Value], "dddd" ),
                BLANK ()
            ),
        "Quarter",
            LOOKUPVALUE (
                'Table'[day],
                'Table'[Status], "Quarter",
                'Table'[day], FORMAT ( [Value], "dddd" ),
                BLANK ()
            )
    ),
    [Full] & [Half] & [Quarter]
        <> BLANK ()
)

Regards,

Xiaoxin Sheng