Help with a Pivot column case

[StefanoGrimaldi]

hey guys, 

here found this little example trying to do but getting a error and was guessing how to solve: 

basically trying to get from here: 

 to here: 

but using the pivot column method getting this error since a column cell get a list value, need how to separete it to diferente columns to avoid the error if possible on the same step I guess: 

let
Source = Excel.Workbook(File.Contents("C:\Users\grima\Downloads\Example.xlsx"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Reference", Int64.Type}, {"Name", type text}, {"Part Number", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Part Type", each if Text.Contains([Part Number], "Opt") then "Alternative" else "Original"),
#"Pivoted Column" = Table.Pivot(#"Added Custom", List.Distinct(#"Added Custom"[#"Part Type"]), "Part Type", "Part Number")
in
#"Pivoted Column"

[AlB · Accepted solution]

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 

[Anonymous · Accepted solution]

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"

[Anonymous]

I added a few lines to make some groups more diverse (because I was trying a solution with the GroupKind.Local option)

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 "Alternate Part" else "Part"),
    #"Sostituito valore" = Table.ReplaceValue(#"Aggiunta colonna personalizzata","Opt: ","",Replacer.ReplaceText,{"Part Number"}),
    #"Riordinate colonne" = Table.ReorderColumns(#"Sostituito valore",{"Reference", "Name", "part", "Part Number"}),
    #"Raggruppate righe" = Table.Group(#"Riordinate colonne", {"Reference"}, {{"name",each _[Name]{0}},{"all", each Table.PromoteHeaders(Table.Transpose(_[[part],[Part Number]]))}}),
    #"Tabella all espansa" = Table.ExpandTableColumn(#"Raggruppate righe", "all", {"Part", "Part_1", "Part_2", "Part_3", "Part_4", "Alternate Part", "Alternate Part_5", "Alternate Part_6", "Alternate Part_7"}, {"Part", "Part_1", "Part_2", "Part_3", "Part_4", "Alternate Part", "Alternate Part_5", "Alternate Part_6", "Alternate Part_7"})
in
    #"Tabella all espansa"

[AlB · Accepted solution]

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