Help !!! Unpivot Columns didn't meet expectation

[Chanleakna123]

hi , i have issue with unpivot the columns , i tried it and no success , due to the Q1 , Q2 and Q1 Comment , Q2 Comment etc.... are combined in the same column which is hard for me to visualized the report . 

1st Pic : the Orginated tables i have , 

2nd Pic : i want to combine in like this , I have Question which contained only Questions , Q1 contained only the number of it . Q1 Comment contains only the Comment , URL , Contained only URL , 

how to success this ? 

[v-danhe-msft]

Hi @Chanleakna123,

Based on my test, you could refer to below formula:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTQNzTXNzIwtFDSUQpLzMurBNK+PgFGQMoAiP3ySxTc8kvzUoBsQyA2AeLg1KLM/NJiqAqQqH82iKNnbqoUq4PbTGOoam9/mGqICe75+TDjUbixsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, Auditor = _t, Location = _t, Q1 = _t, #"Q1 comment" = _t, #"Q1 score" = _t, Q2 = _t, #"Q2 comment" = _t, #"Q2 score" = _t, Q3 = _t, #"Q3 Comment" = _t, #"Q3 Score" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Auditor", type text}, {"Location", type text}, {"Q1", Int64.Type}, {"Q1 comment", type text}, {"Q1 score", type number}, {"Q2", Int64.Type}, {"Q2 comment", type text}, {"Q2 score", Int64.Type}, {"Q3", Int64.Type}, {"Q3 Comment", type text}, {"Q3 Score", type number}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Date", "Auditor", "Location", "Q1 comment", "Q1 score", "Q2 comment", "Q2 score", "Q3 Comment", "Q3 Score"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"Attribute", "QuestionType"}}),
    #"Unpivoted Columns1" = Table.UnpivotOtherColumns(#"Renamed Columns", {"Date", "Auditor", "Location", "Q1 score", "Q2 score", "Q3 Score", "QuestionType", "Value"}, "Attribute", "Value.1"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Columns1", "Custom", each if Text.Start([Attribute],2)=[QuestionType] then 1 else 0),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = 1)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Attribute"}),
    #"Unpivoted Columns2" = Table.UnpivotOtherColumns(#"Removed Columns", {"Date", "Auditor", "Location", "QuestionType", "Value", "Value.1", "Custom"}, "Attribute", "Value.2"),
    #"Added Custom1" = Table.AddColumn(#"Unpivoted Columns2", "Custom.1", each if Text.Start([Attribute],2)=[QuestionType] then 1 else 0),
    #"Filtered Rows1" = Table.SelectRows(#"Added Custom1", each ([Custom.1] = 1))
in
    #"Filtered Rows1"

Result:

You could also download the pbix file to have a view.

Regards,

Daniel He

[Chanleakna123]

hi @v-danhe-msftDanial , 

your M Code is fantastic . but i follow the steps and stuck at Add Custom Column it show only 0 , not 1 , i also wanna follow yours , below is my M Code. can you please test and let me know the issue ? 

let
Source = Csv.Document(Web.Contents("https://docs.google.com/spreadsheets/d/e/2PACX-1vQGwLNNPqXIrMNiEsL06MA2QDR6XPg0huxYPv-JcEx3kxwg8J1CG...",", Columns=60, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers1" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Filtered Rows" = Table.SelectRows(#"Promoted Headers1", each ([FG Transfer] = "FGTransfer_0003")),
#"Unpivoted Only Selected Columns" = Table.Unpivot(#"Filtered Rows", {"Row No. :", "Row No. : 2", "Row No. : 3", "Row No. : 4", "Row No. : 5", "Row No. : 6", "Row No. : 7", "Row No. : 8", "Row No. : 9", "Row No. : 10", "Row No. : 11", "Row No. : 12"}, "Attribute", "Value"),
#"Renamed Columns" = Table.RenameColumns(#"Unpivoted Only Selected Columns",{{"Attribute", "Question Type"}}),
#"Unpivoted Only Selected Columns1" = Table.Unpivot(#"Renamed Columns", {"Pallets :", "Pallets : 2"}, "Attribute.1", "Value.1"),
#"Added Custom" = Table.AddColumn(#"Unpivoted Only Selected Columns1", "Custom", each if Text.Start([Attribute.1],2)=[Question Type] then 1 else 0)
in
#"Added Custom"

[Chanleakna123]

I wanna focus only row and pallet , so need to make sure this row number with this pallet. @v-danhe-msft

[Chanleakna123]

hi @v-danhe-msft  i have link for your correction and help me on this , can you please gently assist me on this ? 
I'm stuck on every side on the unpivot tables now. 

https://drive.google.com/file/d/1mPh8GjFnIHiMuMgaGE_yJniloj1RdO76/view?usp=sharing