cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
laciodrom_80
Helper IV
Helper IV

Create a calculated table

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

Luca
1 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"

 


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

6 REPLIES 6
v-micsh-msft
Microsoft
Microsoft

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.

24.PNG25.PNG26.PNG

27.PNG

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

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

Luca

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"

 


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

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

Luca

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.


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Greg_Deckler
Super User IV
Super User IV

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


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Top Solution Authors
Top Kudoed Authors