RowsToColumns in Power Query

Power Query
1

Hi All,

Thanks for replying the queries.

I am working on sales data and want to convert OrderNumber and Date as row headers .
I have data in the form of transactions.
So,I am attaching the screenshot what I want to achieve.Please suggest how to do in Power Query Editor.

OrderDate
OrderDate958×593 60.6 KB

Ordernumber.xlsx (8.9 KB)

2

Hi Nisha,
here is one possibility:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Added Custom" = Table.AddColumn(Source, "Custom", each if [Column1] = "Ordernumber" then [Column2] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Custom"}),
    #"Pivoted Column" = Table.Pivot(#"Filled Down", List.Distinct(#"Filled Down"[Column1]), "Column1", "Column2"),
    #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Custom"})
in
    #"Removed Columns"

image

Regards,
Matthias

2 Likes
3

Thanks Matthias for your response

But I am not that much good in understanding M code because I am in learning phase.

Could you please explain it through Power Query steps?

4

Nisha, I took an easy version where the step names do explain what is done:

#"Added Custom" => whenever there is an Ordernumber that number is taken
#"Filled Down" => the Ordernumber is filled down
#"Pivoted Column" => rotates the data in Column1 into column headings
#"Removed Columns" => takes out the Custom Column, as it is a not needed anymore

In any case you can copy the code and bring it to the advanced editor and then see step by step what is done. I think that will help you understand.

1 Like
5

Thank you so much Matthias.The problem of transformation of columns has been resolved.

But ,2% Error I can see in my data while performing it .
I have date and Ordernumber both but its showing error in these rows stating

Expression.Error: There weren’t enough elements in the enumeration to complete the operation.
Details:
[List]

Error
Error856×699 73.7 KB

Note:I have checked there is value for both date and order then why I am getting this error? Not sure
Could you please have any suggestion?

6

Nisha, how does the data look in the area of 34012 to 34015?

1 Like
7

Thanks Matthias ,I had duplicate copies in the data source so the issue was there
Its resolved now, thank you so much.

1 Like
8

Thanks Nisha for the feedback. That makes sense.