Hi Mynda,
I'm loving your Power Query course and am so excited about all the possibilities this new found knowledge has opened up! Thank you!
I'm working on one of my projects as I progress through the course but I've come across a data cleaning issue that isn't addressed in your course so I'm hoping for some guidance.
I've attached a dummy file as an example. The data I'm receiving is on the 'Raw Data' tab, the output I'm looking for is on the 'Cleaned Data' tab. Basically, I need to lift and shift columns P-AC into column B-O for rows 5-7. Actual raw data files will have over 14,000 records and this issue is found throughout.
Any help you can provide is appreciated!
Thanks!
Shannon
Hi Shannon,
Keep in mind that Power Query does not preserve all the time the order of the columns , in some cases, after some specific operations not even the rows order will be the same. (you have to add an index column at the very first steps, then sort after this column if the rows are scrambled, in rare cases when you need to have the same order of rows as the initial data)
The columns are sorted alphabetically, so you have to refer to column names instead of column numbers, otherwise you might mess up the data.
Add an index column, then for each column you want to change add a new column to create the final value for that:
= if ([Index]>4 and [Index]<8) then [textbox9] else [textbox7]
This will be the easy way. It is possible to setup a settings table, to indicate the row index, source and destination column names and write the query to transform the existing columns, but it's more complex.
One way is to setup a simple table with indexes of the records that must be changed, create a function that can check if the current row index is in the list of indexes where data must be taken from another column, and just replace the column values.
A function that will check if the current row index is in the indexes table:
(index)=>
let
Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Index", Int64.Type}}),
Index = try (Table.SelectRows(#"Changed Type", each ([Index] = index)){0}[Index]<>null) otherwise false
in
Index
Then use this function in .ReplaceValue function, if the index is in the index table, data will be taken from the other column:
ReplaceColumn3 = Table.ReplaceValue(ReplaceColumn2 ,each [category2], each (if IsInRowsTable([Index]) then [category3] else [category2]), Replacer.ReplaceValue,{"category2"})
File attached. (I replaced only 3 columns, you have to add another step for each column you want to replace.)
Anoher improvement might be to use a row iterator to loop through a table containing the column names that must be replaced, if there are too many columns.
Hi Catalin,
I was working through your index column suggestion but then realized that it won't work because I will be adding data every month and the max index will always be changing.
I'm new to Power Query so it's taking me a bit to wrap my head around the function you suggested. I guess if the indexes table updated every month then this could work...something to play with.
Thanks for the suggestions!
You have to provide a criteria to power query, to identify the rows where data should be taken from another column.
You mentioned row numbers, so I used a row index table to setup the query.
If there is a good indication inside the data table to tell if data should be taken from another column, then we can use that, but you have to identify a good criteria for that.
Success!
= Table.AddColumn(#"Added Conditional Column9", "Custom.10", each if [category2] = null then [Textbox12] else [Textbox10])
I basically recreated all the columns I needed using this criteria.
Thanks for your help!
