Query Editor Unpivot

[haroonali1000]

Hi Guys,

Need a little help, I am trying to Un-pivot my data and want to split it into more than 1 column in the query editor. I have managed to get my attribute and value pairs what i would like to do is split the column into multiple attribute and values.

I currently have the above attribute and value pair. I would ideally like it so that it as below.

Any help would be much appreciated.

Thanks,
H

[Melissa]

Hi @haroonali1000,

Without the data and your previous transformations, I can’t tell if there’s a better solution - but this seems to do the trick…

let
    Source = myTable,
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ProposalID", Int64.Type}, {"EmployeeID", Int64.Type}, {"DateUpdate", type datetime}, {"Attribute", type text}, {"Value", type any}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Mod", each Number.Mod([Index], 3)),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"ProposalID", "EmployeeID", "DateUpdate", "Attribute", "Value", "Mod"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Other Columns", {"Mod"}, "Attribute.1", "Value.1"),
    #"Grouped Rows" = Table.Group(#"Unpivoted Other Columns", {"Mod"}, {{"Count", each _, type table [Mod=number, Attribute.1=text, Value.1=anynonnull]}}),
    #"Added Custom1" = Table.AddColumn( #"Grouped Rows", "Custom", each Table.PromoteHeaders( Table.Transpose(Table.RemoveColumns([Count],"Mod")), [PromoteAllScalars=true])),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom1", "Custom", {"ProposalID", "EmployeeID", "DateUpdate", "Attribute", "Value", "ProposalID_1", "EmployeeID_2", "DateUpdate_3", "Attribute_4", "Value_5", "ProposalID_6", "EmployeeID_7", "DateUpdate_8", "Attribute_9", "Value_10"}, {"ProposalID", "EmployeeID", "DateUpdate", "Attribute", "Value", "ProposalID_1", "EmployeeID_2", "DateUpdate_3", "Attribute_4", "Value_5", "ProposalID_6", "EmployeeID_7", "DateUpdate_8", "Attribute_9", "Value_10"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Expanded Custom",{"ProposalID", "EmployeeID", "DateUpdate", "Attribute", "Value", "Attribute_4", "Value_5", "Attribute_9", "Value_10"})
in
    #"Removed Other Columns1"

Your data is in pairs of three, so I added an Index from 1 and used the MOD to identify the pairs.
Unpivoted Other Columns and Grouped All Rows by MOD
Transformed that table, by first removing the MOD column then Promoting the Headers and finally
Transposing it. Now you’re left with a single row.
Expanded that table and Removed some columns

I hope this is helpful, here’s my file eDNA Forum - Query Editor Unpivot.pbix (41.0 KB)

[ankit]

Hi @haroonali1000

For best practice, always share the sample data in a file instead of a image so it is easier for team to work on the problem.

Also where possible share PBIX file also.

Thanks
Ankit

[haroonali1000]

Hi @Melissa thank you for you response i have tried your method and its pretty much there the only thing i think that isn’t resolved is when a particular employee is in more than one proposal.

@ankit apologies I have now attached the PBIX file.

Thank you for your help .

HA forum.pbix (208.8 KB)

[Melissa]

Please also supply the data in xlsx - thanks

[haroonali1000]

sure please find attached

Forum Test.xlsx (69.9 KB)

[Melissa]

@haroonali1000 thanks for the files.

Came up with this result, can you see if that’s correct? Thank you.

I marked the NULL (text values) above because they are important for the data structure so they must not be null - If so you’ll have to add a step, as indicated by the arrow, below Changed Type where you replace all null with “NULL” (or any other value of your choosing).

Here’s the file, if you update the FileLocation parameter, the queries will be restored.
HA forum.pbix (234.7 KB)

[EnterpriseDNA]

Hi @haroonali1000, we’ve noticed that no response has been received from you since January 10, 2010. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Thanks!