Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

6 REPLIES 6
v-micsh-msft
Employee
Employee

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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Greg_Deckler
Super User
Super User

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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.