Create a calculated table

[laciodrom_80]

Hi guys,

I've got a table like this one:

ID  COLUMN

1        22;25

2         10;5

3         100;70

from which I'd like to create this new one:

ID  COLUMN

1        22

1        25

2         10

2         5

3         100

3         70

What kind of DAX transformation could I apply?

Thanks in advance for any hint

[v-micsh-msft]

Hi laciodrom_80,

I would agree with smoupre.

Under Power BI Query Editor, we could first click on Split Column, choose by Delimeter, this would split the column into two,

Then under Transform tab, choose upviot column, selecting the two splitted column, click upviot. done.

If you would like to split the column using DAX in Data View, try with the following formula in a calculated column:

Please note that even we could seperate the two value in DAX, it is still not available to upviot the column in Data View.

First = PATHITEM(SUBSTITUTE(Table3[Column], ";", "|"), 1)

Second = PATHITEM(SUBSTITUTE(Table3[Column], ";", "|"), 2)

DAX function reference:

SUBSTITUTE Function (DAX)

PATHITEM Function (DAX)

 If you need any further assistance on this topic, please post back.

Regards

[laciodrom_80]

Thanks both @Greg_Deckler and @v-micsh-msft for useful suggestions, but perhaps I have to face the problem differently :

I wish I have a filter (filter1) which displays all description items of these table

TABLE1

ID           ITEMS          TABLE2_IDS

1              Item1             22;25

2              Item2             10;5

3              Item3             100

where TABLE2_IDS contains reference ids to this other table:

TABLE2

ID       NAMES

5           Name1

10         Name2

22         Name3

25         Name4

100       Name5

When I select an Item from the above filter I'd like to display into another filter (filter2) all associated Names in table2

e.g.   If I select Item2 in filter1 I wish to display Name1 and Name2 in filter2

[Greg_Deckler · Accepted solution]

Yes, this worked the way I expected. I used a slicer for ITEM and a table for the Names table and it worked like a champ. Here are the queries I used.

Items table

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUfIsSc0F0UZG1kamSrE60UpGUFEQbWhgDRE0hgoagwUNlGJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, ITEMS = _t, TABLE2_IDS = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"ITEMS", type text}, {"TABLE2_IDS", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type","TABLE2_IDS",Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv),{"TABLE2_IDS.1", "TABLE2_IDS.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"TABLE2_IDS.1", Int64.Type}, {"TABLE2_IDS.2", Int64.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"ID", "ITEMS"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Value", "TABLE2_IDS"}})
in
    #"Renamed Columns"

Names table

let
 Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlXSUfJLzE01VIrViVYyNIByjcBcIyMo1xjChSk2gSqGqTZVio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, NAMES = _t]),
 #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}})
in
 #"Changed Type"

[laciodrom_80]

Thanks, I was wrong setting the cross filter direction in the relationship between the two tables

[Greg_Deckler]

So, split and unpivot the column as described, then import Table 2. Relate the two tables on TABLE2_IDS column (should end up with a single colum for this) and ID column from Table 2. Create a slicer for Table1 Items and you should have what you want. I'll check with the example you have given to see if it works the way I expect.

[Greg_Deckler]

I would suggest that you do that in Power Query by just splitting the column on ";" and then unpivoting the resulting columns.