Changing some Rows of the csv file in to Column for data load

Question

I had a CSV file in the format like below,

Card Name	Sec Name	Question Text	Answer 1	Answer 2
Specialists Offline	Customer Satisfaction	Does the Bill	Yes	No
Specialists Offline	Customer Satisfaction	Do Invoicing Term	Yes	No
Specialists Offline	Customer Satisfaction	Have all appropriate Line	TRUE	FALSE

To normalize the table in the below format

Card Name	Sec Name	Question Text	Answer
Specialists Offline	Customer Satisfaction	Does the Bill	Yes
Specialists Offline	Customer Satisfaction	Does the Bill	No
Specialists Offline	Customer Satisfaction	Do Invoicing Term	Yes
Specialists Offline	Customer Satisfaction	Do Invoicing Term	No
Specialists Offline	Customer Satisfaction	Have all appropriate Line	TRUE
Specialists Offline	Customer Satisfaction	Have all appropriate Line	FALSE

I used the query like below with the help of the experts, it works great

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Card Name", type text}, {"Sec Name", type text}, {"Question Text", type text}, {"Answer 1", type text}, {"Answer 2", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Card Name", "Sec Name", "Question Text"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Value", "Answer"}})
in
    #"Renamed Columns"

But now the table format has changed that the each Answer has attributes associated with it like below

Sec Name	Question Text	Answer 1	Answer 1 Score	Answer 2	Answer 2 Score
Customer Satisfaction	Does the Bill	Yes	10	No	0
Customer Satisfaction	Do Invoicing Term	Yes	5	No	1
Customer Satisfaction	Have all appropriate Line	TRUE	3	FALSE	0

Since each answers have scores associated with it , I need each row with Answers and Score

Sec Name	Question Text	Answer	Answer Score
Customer Satisfaction	Does the Bill	Yes	10
Customer Satisfaction	Does the Bill	No	0
Customer Satisfaction	Do Invoicing Term	Yes	5
Customer Satisfaction	Do Invoicing Term	No	1
Customer Satisfaction	Have all appropriate Line	TRUE	3
Customer Satisfaction	Have all appropriate Line	FALSE	0

So the answer record can be inserted with their scores associated that way it can be easily inserted in to Salesforce. I am very new to the Pivot queries any help is greatly appreciated

Spectral Instance · Accepted Answer · 2023-03-13T01:45:12.297

Try this

let
    TableOne = Table.AddIndexColumn(Table.RemoveColumns(Table.UnpivotOtherColumns(Excel.CurrentWorkbook(){[Name="Table1"]}[Content], {"Sec Name", "Question Text"}, "Attribute", "Value"),{"Attribute"}), "Index", 0, 1, Int64.Type),
    TableTwo = Table.AddIndexColumn(Table.RemoveColumns(TableOne,{"Index"}), "Index", 1, 1, Int64.Type),
    Source = Table.NestedJoin(TableOne, {"Index"}, TableTwo, {"Index"}, "Table2", JoinKind.Inner),
    #"Expanded Table2" = Table.ExpandTableColumn(Source, "Table2", {"Value"}, {"Table2.Value"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Table2", each Number.IsOdd([Index])),
    #"Reordered Columns" = Table.ReorderColumns(#"Filtered Rows",{"Sec Name", "Question Text", "Table2.Value", "Value", "Index"}),
    #"Removed Columns" = Table.RemoveColumns(#"Reordered Columns",{"Index"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Table2.Value", "Answer"}, {"Value", "Answer Score"}})
in
    #"Renamed Columns"

The above is a consolidation of 3 main UI stages:

(1) Create a new query from Table1 on the worksheet, select the first 2 columns and 'Unpivot Other Columns', and then add an Index column (starting from 0)

(2) Reference the query at (1), and add an Index column (starting from 1)

(3) 'Merge as New' the queries at (1) and (2), using an inner join on the index columns added to (1) and (2), and filter for odd numbers in the Index column (you have to apply a sample filter in the UI, and then edit the formula bar to use the Number.IsOdd() syntax) - the rest is just removing, re-naming, and re-ordering columns (if you have similar questions in future it would be in your interest to tag them with powerquery)

Thank you so much.. I thought I could try this with my actual table with the sample I posted above.. But this way advanced query for me to understand :-( If I can post my original table structure in another question can you please guide me — user4912134, Mar 13 '23 at 00:54
@user4912134, I've edited my answer to include the broad UI actions that result in the syntax I originally posted — Spectral Instance, Mar 13 '23 at 01:46
Thank you, Do you think same approach would work in the table https://stackoverflow.com/questions/75717364/normalize-the-table-by-changing-some-rows-in-to-column — user4912134, Mar 13 '23 at 02:04
@user4912134, it was a bit more involved, but I've just posted an answer there, although you'll probably get a better answer from someone else, as the PQ knowledgeable people will have visibility of it, by virtue of the PQ tag — Spectral Instance, Mar 13 '23 at 03:27

Changing some Rows of the csv file in to Column for data load

1 Answers1

Linked