M Code - Custom Column Headache

[lgs1983]

Hi All,

What I can't get my head around is this particular problem.

Child	Parent	Sweden Ind	Full Structure Ind
Amazon UK	Amazon.com	0	1
Amazon Sweden	Amazon.com	1	1
Amazon Paraguay	Amazon.com	0	1

So in the above data we can see the Sweden Ind has a 1 and because of this we need all entities that are part of the same Parent to be marked as one in the Full Structure column. Any ideas if this is possible? Was easy to do in SQL but currently that's not an option.

Thanks in advance.

[Jimmy801 · Accepted solution]

Hello @lgs1983 

you didn't describe the logic why Full Ind is 1 when in the column are also 0. I tried to reproduce your table and the logic is to group by Parent, and then add a new column to the grouped table, that is using the first item in the Ind-column other than 0.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcsxNrMrPUwj1VtKBsvWS83OBHAOlWB24dHB5akpqHroSQ2QlAYlFiemliZVYzIkFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Child = _t, Parent = _t, #"Sweden Ind" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Child", type text}, {"Parent", type text}, {"Sweden Ind", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Parent"}, {{"AllRows", each _, type table [Child=text, Parent=text, Sweden Ind=number]}}),
    FullID = Table.TransformColumns
    (
        #"Grouped Rows",
        {
            {
                "AllRows",
                (tbl)=> Table.AddColumn(tbl, "FullStructure", (add)=> try List.Select(tbl[Sweden Ind], each _ <> 0){0} otherwise null)
            }
        }
    ),
    #"Expanded AllRows" = Table.ExpandTableColumn(FullID, "AllRows", {"Child", "Sweden Ind", "FullStructure"}, {"Child", "Sweden Ind", "FullStructure"})
in
    #"Expanded AllRows"

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[lgs1983]

Thanks for the hard work Jimmy and sorry if I wasn't clear. The Full Ind was to be driven by if one of the subsidiaries of the same parent had a 1 mark all those subsidiaries of said Parent as 1 in the Full Ind column

[AlB]

Hi @lgs1983 

Not clear. What is the exact logic for the 1? The maximum  Sweden Ind for all with the same parent?

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[lgs1983]

That's pretty much it, anything with a 1 in the Sweden indicator means anything with the same parent would have a 1 in the last column.

So if I had an additional set of clients, Blue Print Sweden and Blue Print Norway and they both belongs to Blue Print then they both would have a 1 indicator in that column.