Extracting Data from columns to create other columns

Hi kinga,

You may achieve such a convertion via Power Query:

let
    Source = Excel.Workbook(File.Contents("C:\Users\xxxx\Desktop\Sample Data (Autosaved).xlsx"), null, true),
    Table1_Sheet = Source{[Item="Table1",Kind="Sheet"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Table1_Sheet,{{"Column1", type text}, {"Column2", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"PremiumApprovedBy", type text}, {"PremiumApprovers", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type1", "PremiumApprovers", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"PremiumApprovers.1", "PremiumApprovers.2", "PremiumApprovers.3", "PremiumApprovers.4", "PremiumApprovers.5"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"PremiumApprovers.1", type text}, {"PremiumApprovers.2", type text}, {"PremiumApprovers.3", type text}, {"PremiumApprovers.4", type text}, {"PremiumApprovers.5", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type2", {"PremiumApprovedBy"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}),
    #"Added Custom" = Table.AddColumn(#"Removed Columns", "Level", each if [PremiumApprovedBy]<>null then Text.Start([Value],1) else null),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "IsmultipleIndividuals", each if [PremiumApprovedBy]=null then null else if Text.PositionOf([PremiumApprovedBy],";")>0 then 1 else 0),
    #"Grouped Rows" = Table.Group(#"Added Custom1", {"PremiumApprovedBy"}, {{"Max Level", each List.Max([Level]), type text}, {"All rows", each _, type table}}),
    #"Expanded All rows" = Table.ExpandTableColumn(#"Grouped Rows", "All rows", { "Value", "Level", "IsmultipleIndividuals"}, { "All rows.Value", "All rows.Level", "All rows.IsmultipleIndividuals"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded All rows",{{"All rows.Value", "Value"}, {"All rows.Level", "Level"}, {"All rows.IsmultipleIndividuals", "IsmultipleIndividuals"}}),
    #"Added Conditional Column" = Table.AddColumn(#"Renamed Columns", "flag", each if [IsmultipleIndividuals] = 0 then [Level] else if [IsmultipleIndividuals] = 1 then [Max Level] else null),
    #"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "flag2", each if [flag] = [Level] then 1 else 0),
    #"Filtered Rows" = Table.SelectRows(#"Added Conditional Column1", each ([flag2] = 1)),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"PremiumApprovedBy", "Value"}),
    #"Combine"= Table.Group(#"Removed Other Columns", {"PremiumApprovedBy"}, {{"Column", each Text.Combine([Value], ","), type text}}),
    #"Added Conditional Column2" = Table.AddColumn(Combine, "New PremiumApprover", each if [PremiumApprovedBy] = null then "N/A" else [Column]),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Conditional Column2",{"Column"})
in
    #"Removed Columns1"

I have uploaded the sample .pbix file for your reference. Please check the applied steps in Query Editor mode one by one.

Best regards,

Yuliana Gu