Creating hierarchy

[Anonymous]

Hello community,

could someone supply me a power query m formula which can do the following. I've tried for so long: 

Colum CC-Col should contain all appropriate 25642 values and the result should look like this 

thank you in advance

[smpa01]

@Anonymous  try this

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VcuxCcBADEPRXVS78vnbySzH7b9GTAgEV4InaW/5IkOmRRA69guJT/EOH9Kf/DZvZyoopkRRQ5Lr7s15AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"P-Col" = _t, #"C-Col" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"P-Col", Int64.Type}, {"C-Col", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each let
   Search = #"Changed Type"[#"P-Col"],
   Lookup = #"Changed Type"[#"C-Col"],
   Loop = List.Generate(
                        ()=>[i=List.PositionOf(Search,[#"C-Col"]),j=try Lookup{i} otherwise -999],
                        each [j]<>-999,
                        each [i=[i]+1, j=try Lookup{i} otherwise -999],
                        each [j]
   ) in Loop),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom")
in
    #"Expanded Custom"

[BA_Pete · Accepted solution]

Hi @Anonymous ,

Just merge on C-Col = P-Col Left Outer then expand C-Col from the nested table.

This method gives me the following ouput:

Paste this over the default code in a new blank query to follow the steps I took:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VcuxCcBADEPRXVS78vnbySzH7b9GTAgEV4InaW/5IkOmRRA69guJT/EOH9Kf/DZvZyoopkRRQ5Lr7s15AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"P-Col" = _t, #"C-Col" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"P-Col", Int64.Type}, {"C-Col", Int64.Type}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"C-Col"}, #"Changed Type", {"P-Col"}, "Changed Type", JoinKind.LeftOuter),
    #"Expanded Changed Type" = Table.ExpandTableColumn(#"Merged Queries", "Changed Type", {"C-Col"}, {"C-Col.1"})
in
    #"Expanded Changed Type"

Pete

[AlexisOlson]

I think you should be able to expand CC-Col. If that icon isn't showing you might need to replace those "blank" cells with proper nulls as discussed recently here: https://community.powerbi.com/t5/Desktop/Custom-columns-in-a-SharePoint-Page-Library/m-p/2176571

Take a look at the last two steps in this sample query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjI2NTNR0lEyNjUxBdFKsToIQVMzUyMMQSNTMyN0MaBeMJ2TWVwCkQCKg1SZm5qam8JVwwRNzE3N0QXNTC0sISpjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"P-Col" = _t, #"C-Col" = _t, #"CC-Col" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"P-Col", Int64.Type}, {"C-Col", Int64.Type}, {"CC-Col", type text}}),
    #"Insert List" = Table.TransformColumns(#"Changed Type",{{"CC-Col", each if _ = "list" then {75575,74757,65895} else _}}),
    #"Blank to null" = Table.TransformColumns(#"Insert List",{{"CC-Col", each if Value.Is(_, type list) then _ else null}}),
    #"Expanded CC-Col" = Table.ExpandListColumn(#"Blank to null", "CC-Col")
in
     #"Expanded CC-Col"