I have a few Power Queries where I transpose a table (changing rows into columns), make a few changes (including changing the order of the columns) and then transpose the table back again (changing the columns back to rows). My base data table is variable in the number of records - so when i update the nubmer of rows to transpose to columns and then back to rows changes. This causes the query to break down because it looks for a certain column number which is no longer there. Has anyone come accross this problem & is there a way around this ? Many thanks.
Hi Wim,
Please clarify what changes you need to do, I'm not aware of any transformation that needs transposing twice. Changing the order of the columns can be done without transposing data.
A sample file will be a great help to understand your situation.
Cheers,
Catalin
Hi Wim,
The number of columns argument is optional. Simply delete it from the Transpose.Table formula and it should simply transpose all columns.
Mynda
Hi Catalin,
I will try and add a sample file, but I first need to scramble the data. I will try and explain what I want to accomplish.
The reason I need to transpose twice is because I want to make changes to the column headings. Transforming columns to rows allows me to construct column headings in a single column, using power query functions.
The second transposition allows me to turn my newly constructed column headings (down a single column) into a row which i can then turn into a header row.
This all works fine even when refreshing data, as long as the number of columns stays the same or if you don't change the order of the columns. I understand that I do not need to transpose to change the order of columns - changing the order is what breaks down when my data changes (ie the number of columns changes) since Power Query will add a unique reference to every column and it will return an error if it cannot find back that certain column.
This is a technique I believe that I picked up from one of Mynda's youtube video's - although I cannot seem to find back which one exactly.
Rgds, Wim
PS - added sample file
Hi Wim,
There's no query in this file to show what you're trying to do. When you click the Transpose icon on the Transform tab it doesn't insert a column number argument value into the Table.Transpose formula, so I'm not sure how you're getting the hard keyed column number in there.
Did you try to omit the column number argument like I suggested? I'd like to know if this worked for you before spending more time on this, particularly since I don't know what your query is doing other than transposing.
Thanks,
Mynda
Hi Mynda,
Apologies - the query must have disappeared from the file when I ran the document inspector.
I have added a sample file with the query to this post.
My problem probably is not with the transpose formula (I checked and there is no column number argument).
The problem arises when i want to change the order of the columns after transposing - see below extract from the query.
#"Reordered Columns" = Table.ReorderColumns(#"Added Conditional Column",{"Custom", "Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17", "Column18", "Column19", "Column20", "Column21", "Column22", "Column23", "Column24", "Column25", "Column26", "Column27", "Column28", "Column29"}),
If - when i refresh - the data has grown, this will result in more columns then are mentioned in the query, causing an error and requiring me to add them to the statement above.
Thanks for your help,
Wim
Here is your query modified:
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Filtered Rows" = Table.SelectRows(Source, each ([Column6] <> "IYH1_4025 ")),
#"Removed Top Rows" = Table.Skip(#"Filtered Rows",3),
#"Appended Query" = Table.Combine({Headers, #"Removed Top Rows"}),
#"Promoted Headers" = Table.PromoteHeaders(#"Appended Query", [PromoteAllScalars=true]),
#"Removed Columns1" = Table.RemoveColumns(#"Promoted Headers",{"Overall Result"}),
#"Unpivoted Only Selected Columns1" = Table.Unpivot(#"Removed Columns1", {"XYZ", "ABC", "FKL", "HIO", "DO", "NIL"}, "Attribute", "Value"),
#"Renamed Columns" = Table.RenameColumns(#"Unpivoted Only Selected Columns1",{{"Attribute", "BU"}, {"Value", "Allowance"}})
in
#"Renamed Columns"
As you can see, I removed the first 3 rows, headers are processed separately then appended back.
The Headers query:
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Filtered Rows" = Table.SelectRows(Source, each ([Column6] <> "IYH1_4025 ")),
KeptFirstRows = Table.FirstN(#"Filtered Rows",3),
#"Replaced Value" = Table.ReplaceValue(KeptFirstRows,"",null,Replacer.ReplaceValue,Table.ColumnNames(KeptFirstRows)),
#"Filled Up" = Table.FillUp(#"Replaced Value",Table.ColumnNames(#"Replaced Value")),
#"Kept First Rows1" = Table.FirstN(#"Filled Up",1)
in
#"Kept First Rows1"
You should reorder columns after all renaming operations, but i guess it's not reordering columns now, you have to apply a table sort.
Note that after a step is added, FillUp for example, I edited the M code to replace the hard typed column names with a dynamic list of headers, using Table.ColumnNames.
There is another way to go:
Assuming that your Reorder step has the only intention to bring the Custom column at the beginning of the table, you can:
1. Change the name of the new column to "0" from "Custom":
= Table.AddColumn(#"Transposed Table", "0", each if [Column1] = "" then [Column3] else [Column1])
2. Sort the headers ascending when reordering columns:
= Table.ReorderColumns(#"Added Conditional Column", List.Sort(Table.ColumnNames(#"Added Conditional Column"),Order.Ascending))
This sorting will bring column 0 at the beginning, (as you know, an alphabetical ascending sort will bring numbers before other alphabetic chars), the main advantage is that it does not use hard typed column names.
However, I think the previous solution is better than transposing.
Many thanks Catalin
I will give both options a try - the second one seems to be the easiest to understand but the first one is something I hadn't thought about at all - nice !
