CODEWITHSUNDEEP
powerbi
yogesh puttaswamy
yogesh puttaswamy

Reputation: 71

adding calculated column in power bi (calculated from same table)

Table

I have the above table:

The table can have multiple records for same id (title will be different)

Now i need to add a CALCULATED COLUMN "Parent Title". So, in case parent id is 1, then parent title should be last title of id 1 (Title 11). in case parent id is 2, then parent title should be last title of id 2 (Title 22). and in case parent id is BLANK, then parent title should be BLANK.

How can i achieve this ?

Upvotes: 1

Views: 1048

Answers (2)

Marc Pincince
Marc Pincince

Reputation: 5202

There may be a cleaner way to do it, but I was able to get this via Power Query:

enter image description here

Here's how:

I started with your table (I called it Table1):

enter image description here

Then I used Table1 as a source for creating Table2. (I used "Reference" to do that: I right-clicked on Table1 and selected "Reference" from the drop down.)

enter image description here

Then I transformed Table2 into the following. (I did some sorting first, then some other "fun stuff." You can see what all I did in the M code.)

enter image description here

Here's the M code for Table2:

let
Source = Table1,
#"Sorted Rows" = Table.Sort(Source,{{"Id", Order.Ascending}, {"title", Order.Ascending}, {"Parent Id", Order.Ascending}}),
#"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1),
#"Grouped Rows" = Table.Group(#"Added Index", {"Id"}, {{"MinIndex", each List.Min([Index]), type number}, {"MaxIndex", each List.Max([Index]), type number}, {"AllData", each _, type table}}),
#"Expanded AllData" = Table.ExpandTableColumn(#"Grouped Rows", "AllData", {"title", "Parent Id", "Index"}, {"title", "Parent Id", "Index"}),
#"Added Custom" = Table.AddColumn(#"Expanded AllData", "MaxTitle", each if [MaxIndex]=[Index]then[title]else null),
#"Filled Up" = Table.FillUp(#"Added Custom",{"MaxTitle"}),
#"Added Custom1" = Table.AddColumn(#"Filled Up", "LesserMaxIndex", each [MinIndex]-1)
in
#"Added Custom1"

(You can copy the M code above and paste it over the initial code in Table2's query, which was generated during the "Reference", in the "Advanced Editor"...)

enter image description here

Then I used Table2 as a source for creating Table3. (I used "Reference" again.)

enter image description here

Table3's M code is very simple:

let
Source = Table2
in
Source

And finally, I merged Table2 and Table3 by using "Merge Queries" on the "Home" tab. Specifically, I used "Merge Queries as New".

enter image description here

enter image description here

(Note I matched LesserMaxIndex from Table2 with MaxIndex from Table3 and used a Left Outer join.)

I named the merged query "Merge1".

Then I did some cleanup to Merge1, which you can see in the M code:

let
Source = Table.NestedJoin(Table2,{"LesserMaxIndex"},Table3,{"MaxIndex"},"Table1 (4)",JoinKind.LeftOuter),
#"Expanded Table1 (4)" = Table.ExpandTableColumn(Source, "Table1 (4)", {"MaxTitle"}, {"ParentTitle"}),
#"Sorted Rows" = Table.Sort(#"Expanded Table1 (4)",{{"Id", Order.Ascending}, {"title", Order.Ascending}, {"Parent Id", Order.Ascending}}),
#"Removed Duplicates" = Table.Distinct(#"Sorted Rows"),
#"Removed Other Columns" = Table.SelectColumns(#"Removed Duplicates",{"Id", "title", "Parent Id", "ParentTitle"})
in
#"Removed Other Columns"

Upvotes: 1

teylyn
teylyn

Reputation: 35990

This is fairly easy to do with an Excel worksheet formula along the lines of

=IF(Table1[@[Parent ID]]="","",INDEX(B:B,MATCH(Table1[@[Parent ID]],A:A,1)))

By its nature, the language of PowerPivot/Power BI, i.e. DAX, does not have something like Index/Match or Vlookup, since such constructs are done by relating tables.

If you are in a situation where you want to calculate something like that, it might be wise to re-evaluate your data architecture. Create another query that loads your initial table, sort it, remove the duplicate IDs and keep only the ones you want. Then you can use relationships in Power Pivot or use the query to merge with the original data table.

Upvotes: 0

Related Questions