Assign correct column for value after SplitColumn

[Anonymous]

Hi,

Being new to Power BI I apologise for the terrible subject explanation.

In my Maintable I am splitting a column called "Pillars" by comma. In a PowerApp, a given project can be assigned different "pillars": 1. Service Design, 2. Machine Learning, 3. Strategy and 4. IT

With the split function I now have the columns: Pillar1, Pillar2, Pillar3, Pillar4

However, depending on the choices for a given project, the pillars are not in the order of the numbers as can be seen below:

[rajulshah]

Hello @Anonymous,

Can you please provide the original column used before splitting the columns?

[Anonymous]

Hi @rajulshah ,

This is what the column looks like before splitting:

[rajulshah · Accepted solution]

@Anonymous,
Please refer below link:
https://community.powerbi.com/t5/Desktop/Split-and-rearrange-resulting-columns/td-p/748543

Hope this helps.

If it didn't, please let me know.

[Anonymous]

Hi @rajulshah ,

Thank you for the link, this seems to be what I am looking for. However, being new to Power BI, I am a bit confused with the steps after Unpivoting the "Pillars". I Basically have the steps until dividing into Pillar1, Pillar2, Pillar3 and Pillar4.

It must be from these steps:

#"Split Column by Delimiter" = Table.SplitColumn(#"Renamed Columns", "Reason", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Reason.1", "Reason.2", "Reason.3"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Data.1", type text}, {"Data.2", type text}, {"Reason.1", type text}, {"Reason.2", type text}, {"Reason.3", type text}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"Data", "Data.1", "Data.2"}, "Attribute", "Value"),
#"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"}),
#"Duplicated Column" = Table.DuplicateColumn(#"Removed Columns", "Value", "Value - Copy"),
#"Pivoted Column" = Table.Pivot(#"Duplicated Column", List.Distinct(#"Duplicated Column"[#"Value - Copy"]), "Value - Copy", "Value", List.Max)

in

#"Pivoted Column"

[rajulshah]

@Anonymous,

This is the final query with renaming columns:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTQVQhOLSrLTE5VcEktzkzPU9BRMAIK+iYmZ2TmpSr4pCYW5WXmpQOFjUFqS4oSS1LTKxWUYnWilVBEsOsDqyOCINYoNHWY7sdQhWYREZ7DZpEJkOsZgsPGWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Pillar = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Pillar", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Added Index", "Pillar", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Pillar.1", "Pillar.2", "Pillar.3"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Pillar.1", type text}, {"Pillar.2", type text}, {"Pillar.3", type text}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Changed Type1",{"Index", "Pillar.1", "Pillar.2", "Pillar.3"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Reordered Columns", {"Index"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"}),
    #"Trimmed Text" = Table.TransformColumns(#"Removed Columns",{{"Value", Text.Trim, type text}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Trimmed Text", "Value", "Value - Copy"),
    #"Pivoted Column" = Table.Pivot(#"Duplicated Column", List.Select(List.Distinct(#"Duplicated Column"[#"Value - Copy"]), each _ <> ""), "Value - Copy", "Value", List.Max),
    NewHeaders = List.Transform(Table.ColumnNames(#"Pivoted Column"), (ColumnName) => {ColumnName,try Splitter.SplitTextByAnyDelimiter({"-"})(ColumnName){0} otherwise ColumnName}),
    #"Renamed Columns" = Table.RenameColumns(#"Pivoted Column", NewHeaders),
    #"PrefixColumns" = Table.PrefixColumns(#"Renamed Columns","Pillar")
in
    #"PrefixColumns"

Let me know if this didn't work.

[Anonymous]

Thank you for all you effort @rajulshah !

Without sounding like a complete noob, I am occuring an error with the "Value" column that I understand will be created after pivoting back again. However, it says it does not recognize the column. I have tried inserting each line by line in the M Query, but I don't know what to put in the "In #"XYZ"" afterwards...

This is the code I have put. I wasn't able to insert the after Source line:

let _t = ((type text) meta [Serialized.Text = true]) in type table [Pillar = _t]),

Here it is:

let
    Source = Excel.Workbook(Web.Contents("https://dogmaconsulting-my.sharepoint.com/personal/anders_jensen_dogmaalares_com/Documents/PowerApps/SPM%20for%20PowerApp%20Excelsheet.xlsx"), null, true),
Maintable_SPM_Table = Source{[Item="Maintable_SPM",Kind="Table"]}[Data] ,
#"Changed Type" = Table.TransformColumnTypes(Maintable_SPM_Table,{{"Last Updated", type datetime}, {"Pillars", type text}, {"Practice", type text}, {"Account", type text}, {"Industry", type text}, {"Phase", type text}, {"Opportunity Name", type text}, {"Decision Maker / Sponsor Name", type text}, {"Attitude Towards DA", type text}, {"Opportunity in Short", type text}, {"Estimated Start Time", type datetime}, {"Duration in Days", Int64.Type}, {"Estimated Finish Time", type datetime}, {"SpanningOverMonths", type any}, {"Include in financial forecast", type text}, {"Win Ratio", Int64.Type}, {"Pen Holder", type text}, {"Responsible Partner / Principle", type any}, {"Senior Advisor", type text}, {"Sales Responsible", type text}, {"TechPartners", type text}, {"Reason for Cancel/On hold/Lost", type text}, {"Estimated client budget (TRL)", Int64.Type}, {"First Price (TRL)", Int64.Type}, {"Latest Price (TRL)", Int64.Type}, {"Final Price (TRL)", type any}, {"Current Budget (TRL)", Int64.Type}, {"Weighted Revenue (TRL)", Int64.Type}, {"Expenses (TRL)", Int64.Type}, {"Jan2020", Int64.Type}, {"Feb2020", Int64.Type}, {"Mar2020", Int64.Type}, {"Apr2020", Int64.Type}, {"May2020", Int64.Type}, {"Jun2020", Int64.Type}, {"Jul2020", Int64.Type}, {"Aug2020", Int64.Type}, {"Sep2020", Int64.Type}, {"Oct2020", Int64.Type}, {"Nov2020", Int64.Type}, {"Dec2020", Int64.Type}, {"Jan2021", Int64.Type}, {"Feb2021", Int64.Type}, {"Mar2021", Int64.Type}, {"Apr2021", Int64.Type}, {"May2021", Int64.Type}, {"Jun2021", Int64.Type}, {"Jul2021", Int64.Type}, {"Aug2021", Int64.Type}, {"Sep2021", Int64.Type}, {"Oct2021", Int64.Type}, {"Nov2021", Int64.Type}, {"Dec2021", Int64.Type}, {"__PowerAppsId__", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Last Updated"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Account", "Pillars", "Practice", "Industry", "Phase", "Opportunity Name", "Decision Maker / Sponsor Name", "Attitude Towards DA", "Opportunity in Short", "Estimated Start Time", "Duration in Days", "Estimated Finish Time", "SpanningOverMonths", "Include in financial forecast", "Win Ratio", "Pen Holder", "Responsible Partner / Principle", "Senior Advisor", "Sales Responsible", "TechPartners", "Reason for Cancel/On hold/Lost", "Estimated client budget (TRL)", "First Price (TRL)", "Latest Price (TRL)", "Final Price (TRL)", "Current Budget (TRL)", "Weighted Revenue (TRL)", "Expenses (TRL)", "Jan2020", "Feb2020", "Mar2020", "Apr2020", "May2020", "Jun2020", "Jul2020", "Aug2020", "Sep2020", "Oct2020", "Nov2020", "Dec2020", "Jan2021", "Feb2021", "Mar2021", "Apr2021", "May2021", "Jun2021", "Jul2021", "Aug2021", "Sep2021", "Oct2021", "Nov2021", "Dec2021", "__PowerAppsId__"}),

#"Removed Columns1" = Table.RemoveColumns(#"Reordered Columns",{"Opportunity in Short"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns1",{{"Estimated client budget (TRL)", Currency.Type}, {"First Price (TRL)", Currency.Type}, {"Latest Price (TRL)", Currency.Type}, {"Final Price (TRL)", Currency.Type}, {"Current Budget (TRL)", Currency.Type}, {"Weighted Revenue (TRL)", Currency.Type}, {"Expenses (TRL)", Currency.Type}, {"Jan2020", Currency.Type}, {"Feb2020", Currency.Type}, {"Mar2020", Currency.Type}, {"Apr2020", Currency.Type}, {"May2020", Currency.Type}, {"Jun2020", Currency.Type}, {"Jul2020", Currency.Type}, {"Aug2020", Currency.Type}, {"Sep2020", Currency.Type}, {"Oct2020", Currency.Type}, {"Nov2020", Currency.Type}, {"Dec2020", Currency.Type}, {"Jan2021", Currency.Type}, {"Feb2021", Currency.Type}, {"Mar2021", Currency.Type}, {"Apr2021", Currency.Type}, {"May2021", Currency.Type}, {"Jun2021", Currency.Type}, {"Jul2021", Currency.Type}, {"Aug2021", Currency.Type}, {"Sep2021", Currency.Type}, {"Oct2021", Currency.Type}, {"Nov2021", Currency.Type}, {"Dec2021", Currency.Type}}),

#"Changed Type2" = Table.TransformColumnTypes(Source,{{"Pillars", type text}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1),
#"Split Column by Delimiter" = Table.SplitColumn(#"Added Index", "Pillars", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Pillar.1", "Pillar.2", "Pillar.3", "Pillar.4"}),
#"Changed Type3" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Pillar.1", type text}, {"Pillar.2", type text}, {"Pillar.3", type text}}),
#"Reordered Column2" = Table.ReorderColumns(#"Changed Type1",{"Index", "Pillar.1", "Pillar.2", "Pillar.3", "Pillar.4"}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Reordered Columns", {"Index"}, "Attribute", "Value"),
#"Removed Columns2" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"}),
#"Trimmed Text" = Table.TransformColumns(#"Removed Columns",{{"Value", Text.Trim, type text}}),
#"Duplicated Column" = Table.DuplicateColumn(#"Trimmed Text", "Value", "Value - Copy"),
#"Pivoted Column" = Table.Pivot(#"Duplicated Column", List.Select(List.Distinct(#"Duplicated Column"[#"Value - Copy"]), each _ <> ""), "Value - Copy", "Value", List.Max),
    NewHeaders = List.Transform(Table.ColumnNames(#"Pivoted Column"), (ColumnName) => {ColumnName,try Splitter.SplitTextByAnyDelimiter({"-"})(ColumnName){0} otherwise ColumnName}),
    #"Renamed Columns" = Table.RenameColumns(#"Pivoted Column", NewHeaders),
    #"PrefixColumns" = Table.PrefixColumns(#"Renamed Columns","Pillars")
in
    #"PrefixColumns"

[rajulshah]

@Anonymous,

I can see where this query is breaking and I am resolving it, but errors may occur further. Better would be that you provide sample excel data.

Or use below query:

let
    Source = Excel.Workbook(Web.Contents("https://dogmaconsulting-my.sharepoint.com/personal/anders_jensen_dogmaalares_com/Documents/PowerApps/SPM%20for%20PowerApp%20Excelsheet.xlsx"), null, true),
Maintable_SPM_Table = Source{[Item="Maintable_SPM",Kind="Table"]}[Data] ,
#"Changed Type" = Table.TransformColumnTypes(Maintable_SPM_Table,{{"Last Updated", type datetime}, {"Pillars", type text}, {"Practice", type text}, {"Account", type text}, {"Industry", type text}, {"Phase", type text}, {"Opportunity Name", type text}, {"Decision Maker / Sponsor Name", type text}, {"Attitude Towards DA", type text}, {"Opportunity in Short", type text}, {"Estimated Start Time", type datetime}, {"Duration in Days", Int64.Type}, {"Estimated Finish Time", type datetime}, {"SpanningOverMonths", type any}, {"Include in financial forecast", type text}, {"Win Ratio", Int64.Type}, {"Pen Holder", type text}, {"Responsible Partner / Principle", type any}, {"Senior Advisor", type text}, {"Sales Responsible", type text}, {"TechPartners", type text}, {"Reason for Cancel/On hold/Lost", type text}, {"Estimated client budget (TRL)", Int64.Type}, {"First Price (TRL)", Int64.Type}, {"Latest Price (TRL)", Int64.Type}, {"Final Price (TRL)", type any}, {"Current Budget (TRL)", Int64.Type}, {"Weighted Revenue (TRL)", Int64.Type}, {"Expenses (TRL)", Int64.Type}, {"Jan2020", Int64.Type}, {"Feb2020", Int64.Type}, {"Mar2020", Int64.Type}, {"Apr2020", Int64.Type}, {"May2020", Int64.Type}, {"Jun2020", Int64.Type}, {"Jul2020", Int64.Type}, {"Aug2020", Int64.Type}, {"Sep2020", Int64.Type}, {"Oct2020", Int64.Type}, {"Nov2020", Int64.Type}, {"Dec2020", Int64.Type}, {"Jan2021", Int64.Type}, {"Feb2021", Int64.Type}, {"Mar2021", Int64.Type}, {"Apr2021", Int64.Type}, {"May2021", Int64.Type}, {"Jun2021", Int64.Type}, {"Jul2021", Int64.Type}, {"Aug2021", Int64.Type}, {"Sep2021", Int64.Type}, {"Oct2021", Int64.Type}, {"Nov2021", Int64.Type}, {"Dec2021", Int64.Type}, {"__PowerAppsId__", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Last Updated"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Account", "Pillars", "Practice", "Industry", "Phase", "Opportunity Name", "Decision Maker / Sponsor Name", "Attitude Towards DA", "Opportunity in Short", "Estimated Start Time", "Duration in Days", "Estimated Finish Time", "SpanningOverMonths", "Include in financial forecast", "Win Ratio", "Pen Holder", "Responsible Partner / Principle", "Senior Advisor", "Sales Responsible", "TechPartners", "Reason for Cancel/On hold/Lost", "Estimated client budget (TRL)", "First Price (TRL)", "Latest Price (TRL)", "Final Price (TRL)", "Current Budget (TRL)", "Weighted Revenue (TRL)", "Expenses (TRL)", "Jan2020", "Feb2020", "Mar2020", "Apr2020", "May2020", "Jun2020", "Jul2020", "Aug2020", "Sep2020", "Oct2020", "Nov2020", "Dec2020", "Jan2021", "Feb2021", "Mar2021", "Apr2021", "May2021", "Jun2021", "Jul2021", "Aug2021", "Sep2021", "Oct2021", "Nov2021", "Dec2021", "__PowerAppsId__"}),

#"Removed Columns1" = Table.RemoveColumns(#"Reordered Columns",{"Opportunity in Short"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns1",{{"Estimated client budget (TRL)", Currency.Type}, {"First Price (TRL)", Currency.Type}, {"Latest Price (TRL)", Currency.Type}, {"Final Price (TRL)", Currency.Type}, {"Current Budget (TRL)", Currency.Type}, {"Weighted Revenue (TRL)", Currency.Type}, {"Expenses (TRL)", Currency.Type}, {"Jan2020", Currency.Type}, {"Feb2020", Currency.Type}, {"Mar2020", Currency.Type}, {"Apr2020", Currency.Type}, {"May2020", Currency.Type}, {"Jun2020", Currency.Type}, {"Jul2020", Currency.Type}, {"Aug2020", Currency.Type}, {"Sep2020", Currency.Type}, {"Oct2020", Currency.Type}, {"Nov2020", Currency.Type}, {"Dec2020", Currency.Type}, {"Jan2021", Currency.Type}, {"Feb2021", Currency.Type}, {"Mar2021", Currency.Type}, {"Apr2021", Currency.Type}, {"May2021", Currency.Type}, {"Jun2021", Currency.Type}, {"Jul2021", Currency.Type}, {"Aug2021", Currency.Type}, {"Sep2021", Currency.Type}, {"Oct2021", Currency.Type}, {"Nov2021", Currency.Type}, {"Dec2021", Currency.Type}}),

#"Changed Type2" = Table.TransformColumnTypes(#"Changed Type1",{{"Pillars", type text}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1),
#"Split Column by Delimiter" = Table.SplitColumn(#"Added Index", "Pillars", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Pillar.1", "Pillar.2", "Pillar.3", "Pillar.4"}),
#"Changed Type3" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Pillar.1", type text}, {"Pillar.2", type text}, {"Pillar.3", type text}}),
#"Reordered Column2" = Table.ReorderColumns(#"Changed Type1",{"Index", "Pillar.1", "Pillar.2", "Pillar.3", "Pillar.4"}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Reordered Columns", {"Index"}, "Attribute", "Value"),
#"Removed Columns2" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"}),
#"Trimmed Text" = Table.TransformColumns(#"Removed Columns",{{"Value", Text.Trim, type text}}),
#"Duplicated Column" = Table.DuplicateColumn(#"Trimmed Text", "Value", "Value - Copy"),
#"Pivoted Column" = Table.Pivot(#"Duplicated Column", List.Select(List.Distinct(#"Duplicated Column"[#"Value - Copy"]), each _ <> ""), "Value - Copy", "Value", List.Max),
    NewHeaders = List.Transform(Table.ColumnNames(#"Pivoted Column"), (ColumnName) => {ColumnName,try Splitter.SplitTextByAnyDelimiter({"-"})(ColumnName){0} otherwise ColumnName}),
    #"Renamed Columns" = Table.RenameColumns(#"Pivoted Column", NewHeaders),
    #"PrefixColumns" = Table.PrefixColumns(#"Renamed Columns","Pillars")
in
    #"PrefixColumns"

[Anonymous]

It still gives the same error:

"Expression.Error: The column 'Value' of the table wasn't found.
Details:
Value"

[rajulshah]

@Anonymous,

Please try following query:

let
    Source = Excel.Workbook(Web.Contents("https://dogmaconsulting-my.sharepoint.com/personal/anders_jensen_dogmaalares_com/Documents/PowerApps/SPM%20for%20PowerApp%20Excelsheet.xlsx"), null, true),
Maintable_SPM_Table = Source{[Item="Maintable_SPM",Kind="Table"]}[Data] ,
#"Changed Type" = Table.TransformColumnTypes(Maintable_SPM_Table,{{"Last Updated", type datetime}, {"Pillars", type text}, {"Practice", type text}, {"Account", type text}, {"Industry", type text}, {"Phase", type text}, {"Opportunity Name", type text}, {"Decision Maker / Sponsor Name", type text}, {"Attitude Towards DA", type text}, {"Opportunity in Short", type text}, {"Estimated Start Time", type datetime}, {"Duration in Days", Int64.Type}, {"Estimated Finish Time", type datetime}, {"SpanningOverMonths", type any}, {"Include in financial forecast", type text}, {"Win Ratio", Int64.Type}, {"Pen Holder", type text}, {"Responsible Partner / Principle", type any}, {"Senior Advisor", type text}, {"Sales Responsible", type text}, {"TechPartners", type text}, {"Reason for Cancel/On hold/Lost", type text}, {"Estimated client budget (TRL)", Int64.Type}, {"First Price (TRL)", Int64.Type}, {"Latest Price (TRL)", Int64.Type}, {"Final Price (TRL)", type any}, {"Current Budget (TRL)", Int64.Type}, {"Weighted Revenue (TRL)", Int64.Type}, {"Expenses (TRL)", Int64.Type}, {"Jan2020", Int64.Type}, {"Feb2020", Int64.Type}, {"Mar2020", Int64.Type}, {"Apr2020", Int64.Type}, {"May2020", Int64.Type}, {"Jun2020", Int64.Type}, {"Jul2020", Int64.Type}, {"Aug2020", Int64.Type}, {"Sep2020", Int64.Type}, {"Oct2020", Int64.Type}, {"Nov2020", Int64.Type}, {"Dec2020", Int64.Type}, {"Jan2021", Int64.Type}, {"Feb2021", Int64.Type}, {"Mar2021", Int64.Type}, {"Apr2021", Int64.Type}, {"May2021", Int64.Type}, {"Jun2021", Int64.Type}, {"Jul2021", Int64.Type}, {"Aug2021", Int64.Type}, {"Sep2021", Int64.Type}, {"Oct2021", Int64.Type}, {"Nov2021", Int64.Type}, {"Dec2021", Int64.Type}, {"__PowerAppsId__", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Last Updated"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Account", "Pillars", "Practice", "Industry", "Phase", "Opportunity Name", "Decision Maker / Sponsor Name", "Attitude Towards DA", "Opportunity in Short", "Estimated Start Time", "Duration in Days", "Estimated Finish Time", "SpanningOverMonths", "Include in financial forecast", "Win Ratio", "Pen Holder", "Responsible Partner / Principle", "Senior Advisor", "Sales Responsible", "TechPartners", "Reason for Cancel/On hold/Lost", "Estimated client budget (TRL)", "First Price (TRL)", "Latest Price (TRL)", "Final Price (TRL)", "Current Budget (TRL)", "Weighted Revenue (TRL)", "Expenses (TRL)", "Jan2020", "Feb2020", "Mar2020", "Apr2020", "May2020", "Jun2020", "Jul2020", "Aug2020", "Sep2020", "Oct2020", "Nov2020", "Dec2020", "Jan2021", "Feb2021", "Mar2021", "Apr2021", "May2021", "Jun2021", "Jul2021", "Aug2021", "Sep2021", "Oct2021", "Nov2021", "Dec2021", "__PowerAppsId__"}),

#"Removed Columns1" = Table.RemoveColumns(#"Reordered Columns",{"Opportunity in Short"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns1",{{"Estimated client budget (TRL)", Currency.Type}, {"First Price (TRL)", Currency.Type}, {"Latest Price (TRL)", Currency.Type}, {"Final Price (TRL)", Currency.Type}, {"Current Budget (TRL)", Currency.Type}, {"Weighted Revenue (TRL)", Currency.Type}, {"Expenses (TRL)", Currency.Type}, {"Jan2020", Currency.Type}, {"Feb2020", Currency.Type}, {"Mar2020", Currency.Type}, {"Apr2020", Currency.Type}, {"May2020", Currency.Type}, {"Jun2020", Currency.Type}, {"Jul2020", Currency.Type}, {"Aug2020", Currency.Type}, {"Sep2020", Currency.Type}, {"Oct2020", Currency.Type}, {"Nov2020", Currency.Type}, {"Dec2020", Currency.Type}, {"Jan2021", Currency.Type}, {"Feb2021", Currency.Type}, {"Mar2021", Currency.Type}, {"Apr2021", Currency.Type}, {"May2021", Currency.Type}, {"Jun2021", Currency.Type}, {"Jul2021", Currency.Type}, {"Aug2021", Currency.Type}, {"Sep2021", Currency.Type}, {"Oct2021", Currency.Type}, {"Nov2021", Currency.Type}, {"Dec2021", Currency.Type}}),



      #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Pillars", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Pillar.1", "Pillar.2", "Pillar.3"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Pillar.1", type text}, {"Pillar.2", type text}, {"Pillar.3", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"Last Updated", "Practice", "Account", "Industry", "Phase", "Opportunity Name", "Decision Maker / Sponsor Name", "Attitude Towards DA", "Opportunity in Short", "Estimated Start Time", "Duration in Days", "Estimated Finish Time", "SpanningOverMonths", "Include in financial forecast", "Win Ratio", "Pen Holder", "Responsible Partner / Principle", "Senior Advisor", "Sales Responsible", "TechPartners", "Reason for Cancel/On hold/Lost", "Estimated client budget (TRL)", "First Price (TRL)", "Latest Price (TRL)", "Final Price (TRL)", "Current Budget (TRL)", "Weighted Revenue (TRL)", "Expenses (TRL)", "Jan2020", "Feb2020", "Mar2020", "Apr2020", "May2020", "Jun2020", "Jul2020", "Aug2020", "Sep2020", "Oct2020", "Nov2020", "Dec2020", "Jan2021", "Feb2021", "Mar2021", "Apr2021", "May2021", "Jun2021", "Jul2021", "Aug2021", "Sep2021", "Oct2021", "Nov2021", "Dec2021", "__PowerAppsId__"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}),
    #"Trimmed Text" = Table.TransformColumns(#"Removed Columns",{{"Value", Text.Trim, type text}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Trimmed Text", "Value", "Value - Copy"),
    #"Pivoted Column" = Table.Pivot(#"Duplicated Column", List.Select(List.Distinct(#"Duplicated Column"[#"Value - Copy"]), each _ <> ""), "Value - Copy", "Value", List.Max),
    NewHeaders = List.Transform(Table.ColumnNames(#"Pivoted Column"), (ColumnName) => {ColumnName,try Splitter.SplitTextByAnyDelimiter({" -"})(ColumnName){0} otherwise ColumnName}),
    #"Renamed Columns" = Table.RenameColumns(#"Pivoted Column", NewHeaders)
in
    #"Renamed Columns"

Please let me know.

[Anonymous]

@rajulshah 

For some reason it still gives the same error.

[rajulshah]

Can you provide sample data?

[Anonymous]

@rajulshah 

How do I do that?

[rajulshah]

You can upload it on OneDrive or Google Sheets.

[rajulshah]

Hello @Anonymous,

Please use the following M query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTQVQhOLSrLTE5VcEktzkzPU9BRMAIK+iYmZ2TmpSr4pCYW5WXmpQOFjUFqS4oSS1LTKxWUYnWilVBEsOsDqyOCINYoNHWY7sdQhWYREZ7DZpEJkOsZgsPGWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Pillar = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Pillar", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Added Index", "Pillar", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Pillar.1", "Pillar.2", "Pillar.3"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Pillar.1", type text}, {"Pillar.2", type text}, {"Pillar.3", type text}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Changed Type1",{"Index", "Pillar.1", "Pillar.2", "Pillar.3"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Reordered Columns", {"Index"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"}),
    #"Trimmed Text" = Table.TransformColumns(#"Removed Columns",{{"Value", Text.Trim, type text}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Trimmed Text", "Value", "Value - Copy"),
    #"Pivoted Column" = Table.Pivot(#"Duplicated Column", List.Select(List.Distinct(#"Duplicated Column"[#"Value - Copy"]), each _ <> ""), "Value - Copy", "Value", List.Max)
in
    #"Pivoted Column"

Please note that I have used "Index" column as ID. You can use your column. Also, in the end, you would need to rename columns.

I am trying to automate the logic of renaming columns. I'll let you know.