Get the Data from Parent Field

[cp1985]

Hello,

I have following sample data in Power BI for Azure Cost management. I need to get value from RG column for all resources which has blank Department value. Problem I have is for example,  sqltest1 parent resource for all sqltest1 sub resources is in Infra-RG and Others RG. I need value from Infra-RG RG only as it is parent RG for sqltest1 resource to give accurate cost. 

Desired Output should look like below.

I wrote a DAX query to get value from parent RG, but that gives me value from Others RG instead of Infra-RG for email and network resources for sqltest1 parent Resource which I don't want. 

Column = CALCULATE(FIRSTNONBLANK('Sheet1'[Department],1),FILTER('Sheet1',[RG]=EARLIER('Sheet1'[RG])&&'Sheet1'[Department]<>BLANK()))

How do I get my desired output? any help appreciated. I am at very beginner level with Power BI. 

[dax]

Hi @cp1985 , 

You could try below M code to see whether it work or not

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8sxLK0rUDXJX0oEwgXRxYU5JanGJITIzVgdFKVZVRkSpMkZRZYRkb5ivoZExnCasytjEFEMVBIX5mptbWOKUNDE1A8slwyScHcHiIa7BIYZILBQ1KCocfUJcg4LBCvJLMlKLisEORPWvq6+jpw+qElQFfq4h4f5B3kqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [RG = _t, Department = _t, ResourceName = _t, Resource = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"RG", type text}, {"Department", type text}, {"ResourceName", type text}, {"Resource", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ResourceName"}, {{"Count", each List.Max([Department]), type text}, {"ALL", each _, type table [RG=text, Department=text, ResourceName=text, Resource=text]}}),
    #"Expanded ALL" = Table.ExpandTableColumn(#"Grouped Rows", "ALL", {"RG", "Department", "Resource"}, {"RG", "Department", "Resource"}),
    #"Grouped Rows1" = Table.Group(#"Expanded ALL", {"RG"}, {{"MAX", each List.Max([Count]), type text}, {"ALL", each _, type table [ResourceName=text, Count=text, RG=text, Department=text, Resource=text]}}),
    #"Expanded ALL1" = Table.ExpandTableColumn(#"Grouped Rows1", "ALL", {"ResourceName", "Department", "Resource"}, {"ResourceName", "Department", "Resource"})
in
    #"Expanded ALL1"

Or

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8sxLK0rUDXJX0oEwgXRxYU5JanGJITIzVgdFKVZVRkSpMkZRZYRkb5ivoZExnCasytjEFEMVBIX5mptbWOKUNDE1A8slwyScHcHiIa7BIYZILBQ1KCocfUJcg4LBCvJLMlKLisEORPWvq6+jpw+qElQFfq4h4f5B3kqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [RG = _t, Department = _t, ResourceName = _t, Resource = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"RG", type text}, {"Department", type text}, {"ResourceName", type text}, {"Resource", type text}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"ResourceName", Order.Descending},{"Department", Order.Descending}}),
    #"Replaced Value" = Table.ReplaceValue(#"Sorted Rows","",null,Replacer.ReplaceValue,{"Department"}),
    #"Filled Down" = Table.FillDown(#"Replaced Value",{"Department"}),
    #"Grouped Rows" = Table.Group(#"Filled Down", {"ResourceName"}, {{"max", each List.Max([Department]), type text}, {"all", each _, type table [RG=text, Department=text, ResourceName=text, Resource=text]}}),
    #"Expanded all" = Table.ExpandTableColumn(#"Grouped Rows", "all", {"RG", "Resource"}, {"RG", "Resource"})
in
    #"Expanded all"

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[cp1985]

@dax,

Thank you for your reply. Your query works for my sample but does not work correctly for my production data.

I have 55 columns and ~500k rows already for the year in my table. It takes too long to manipulate data this way and output is also not accurate.

Not sure how can I achieve what I am looking for. any other idea?