Help with a Pivot column case

Hi StefanoGrimaldi 

Please always show your sample data in text-tabular format in addition to (or instead of) the screen captures. A screen cap doesn't allow people to readily copy the data and run a quick test and thus decreases the likelihood of your question being answered. Just use 'Copy table' in Power BI and paste it here. Or, ideally, share the pbix (beware of confidential data).

Place the following M code in a blank query to see the steps.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXLOSU3My8xLVyjJAJJAAUOlWB0cUka4pSw88MiF4JFzwi3nX1BipWBpQUCBMSEFln6ETDAFKzAC8UsyUovQHWcMZAYkFpUgeTYEuwTYOCcvpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Reference = _t, Name = _t, #"Part Number" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Reference", Int64.Type}, {"Name", type text}, {"Part Number", type text}}),

    #"Grouped Rows" = Table.Group(#"Changed Type", {"Reference", "Name"}, {{"AllList", each [Part Number], type table [Reference=nullable number, Name=nullable text, Part Number=nullable text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Part", each Text.Combine(List.Select([AllList], each not Text.Contains(_,"Opt:")), ","), type text),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Alternate Part", each Text.Combine(List.Transform(List.Select([AllList], each Text.Contains(_,"Opt:")), each Text.AfterDelimiter(_,"Opt: ")), ","), type text),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Added Custom1", "Part", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Part.1", "Part.2", "Part.3", "Part.4", "Part.5"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Part.1", type text}, {"Part.2", Int64.Type}, {"Part.3", type text}, {"Part.4", type text}, {"Part.5", type text}}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type1", "Alternate Part", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Alternate Part.1", "Alternate Part.2", "Alternate Part.3", "Alternate Part.4"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Alternate Part.1", type text}, {"Alternate Part.2", Int64.Type}, {"Alternate Part.3", type text}, {"Alternate Part.4", Int64.Type}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type2",{"AllList"})
in
    #"Removed Columns"

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 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXLOSU3My8xLVyjJAJJAAUOlWB0cUka4pSw88MiF4JFzwi3nX1BipWBpQUCBMSEFln6ETDAFKzAC8UsyUovQHYcpAdZmFgSWNQbyAxKLSpCCIgS7BFiXkxceSXOgzlgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Reference = _t, Name = _t, #"Part Number" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Reference", Int64.Type}, {"Name", type text}, {"Part Number", type text}}),
    #"Aggiunta colonna personalizzata" = Table.AddColumn(#"Changed Type", "part", each if Text.Contains([Part Number],":") then "opt part" else "part"),
    #"Sostituito valore" = Table.ReplaceValue(#"Aggiunta colonna personalizzata","Opt: ","",Replacer.ReplaceText,{"Part Number"}),
    #"Raggruppate righe" = Table.Group(#"Sostituito valore", {"Reference", "part"}, {{"name", each _[Name]{0}},{"all", each Table.PromoteHeaders(Table.Transpose(_[[part],[Part Number]]))}},GroupKind.Local,(x,y)=>if x[Reference]=y[Reference] and not Text.Contains(y[part],"opt") then 0 else if Text.Contains(x[part],"opt") and Text.Contains(y[part],"opt")then 0 else 1),
    #"Tabella all espansa" = Table.ExpandTableColumn(#"Raggruppate righe", "all", {"part", "part_1", "part_2", "part_3", "part_4", "opt part", "opt part_1", "opt part_2", "opt part_3"}, {"part.1", "part_1", "part_2", "part_3", "part_4", "opt part", "opt part_1", "opt part_2", "opt part_3"})
in
    #"Tabella all espansa"

a transformation partially different from the desired one. But it can be adapted. 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXLOSU3My8xLVyjJAJJAAUOlWB0cUka4pSw88MiF4JFzwi3nX1BipWBpQUCBMSEFln6ETDAFKzAC8UsyUovQHYcpAdZmFgSWNQbyAxKLSpCCIgS7BFiXkxceSXOgzlgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Reference = _t, Name = _t, #"Part Number" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Reference", Int64.Type}, {"Name", type text}, {"Part Number", type text}}),
    #"Aggiunta colonna personalizzata" = Table.AddColumn(#"Changed Type", "part", each if Text.Contains([Part Number],":") then "opt part" else "part"),
    #"Sostituito valore" = Table.ReplaceValue(#"Aggiunta colonna personalizzata","Opt: ","",Replacer.ReplaceText,{"Part Number"}),
    #"Raggruppate righe" = Table.Group(#"Sostituito valore", {"Reference", "part"}, {{"name", each _[Name]{0}},{"rec", each Table.PromoteHeaders(Table.Transpose(_[[part],[Part Number]])){0}}},GroupKind.Local,(x,y)=>if x[Reference]=y[Reference] and not Text.Contains(y[part],"opt") then 0 else if Text.Contains(x[part],"opt") and Text.Contains(y[part],"opt")then 0 else 1),
        #"Raggruppate righe1" = Table.Group(#"Raggruppate righe", {"Reference"}, {{"Name", each [name]{0}},{"all", each Record.Combine(_[rec])}}),
    #"Tabella all espansa" = Table.ExpandRecordColumn(#"Raggruppate righe1", "all", {"part", "part_1", "part_2", "part_3", "part_4", "opt part", "opt part_1", "opt part_2", "opt part_3"}, {"part", "part_1", "part_2", "part_3", "part_4", "opt part", "opt part_1", "opt part_2", "opt part_3"})
in
    #"Tabella all espansa"