Hi
anyone know how to use power query to move columns? pls refer attachment.
Bro @rahulshewale1, do you know?
Thanks
anyone know how to use power query to move columns? pls refer attachment.
Bro @rahulshewale1, do you know?
Thanks
= if [Column2] <> null then [Index] else null
= Text.Combine([NewCol][Column1], ",")
Curious, how is that data is entered/brought in? It really lacks structure and missing some fields, it looks like. At any rate, you can follow steps below.
1. Select data range and load from table (make sure My data has header is unchecked)
2. Add index column.
3. Add custom column with following formula.
4. Select the added custom column and "Fill Down"Code:= if [Column2] <> null then [Index] else null
5. Select the custom column and Group by -> All Rows (I named generated column [NewCol])
6. Add custom column with following formula
7. Expand [NewCol] for Column2 only.Code:= Text.Combine([NewCol][Column1], ",")
8. Group by on Custom.1 (combined text column in step 6), and max of Column2.
9. Split Custom.1 by delimiter.
View attachment 46983
10. Rename columns as desired.
Load result to desired location.
Curious, how is that data is entered/brought in? It really lacks structure and missing some fields, it looks like. At any rate, you can follow steps below.
1. Select data range and load from table (make sure My data has header is unchecked)
2. Add index column.
3. Add custom column with following formula.
4. Select the added custom column and "Fill Down"Code:= if [Column2] <> null then [Index] else null
5. Select the custom column and Group by -> All Rows (I named generated column [NewCol])
6. Add custom column with following formula
7. Expand [NewCol] for Column2 only.Code:= Text.Combine([NewCol][Column1], ",")
8. Group by on Custom.1 (combined text column in step 6), and max of Column2.
9. Split Custom.1 by delimiter.
View attachment 46983
10. Rename columns as desired.
Load result to desired location.
Umm, that's completely different from what you posted as initial sample.
I'll need more than one set of sample to ensure logic is correct.
Please upload sample with at least 5 set of data.
= if [Column1] = null then null else [Index]
= Text.Combine([AllRow][Column4],"#(lf)")
= Table.SplitColumn(#"Filtered Rows", "Column2", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv))
Sorry for late reply. I've been away without access to PC.
Here's the steps assuming sample accurately reflects your actual data.
After loading table to PQ using previous post...
1. Add index column.
2. Add custom column with following formula.
3. Fill down on added custom column.Code:= if [Column1] = null then null else [Index]
4. Group by on custom column with no aggregation (i.e. All Rows) and name the new column [AllRow]
5. Add custom column with following function (to combine Column4 texts by group and using #(lf) line change character).
6. Rename custom column above to Column4Code:= Text.Combine([AllRow][Column4],"#(lf)")
7. Expand [AllRow], excluding Column4, Index, & Custom.
8. Move Column4 to appropriate position in the table
9. Change all columns to text type
10. Remove Custom Column used for grouping.
11. Filter out null rows on Column1
12. Split Column2 using following formula
13. Do same operation for Column3, 4, 6, 7 & 9. Remembering to replace #"Filtered Rows" with actual step before each operation and change "Column2" to appropriate column.Code:= Table.SplitColumn(#"Filtered Rows", "Column2", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv))
See attached.
I'm not sure what you are asking here...why after click will auto got number, and why is that number?
As for AllRow producing duplicate, that happens since expanding table is like doing full join on two table (SQL term). Hence, you filter out null rows (in step 11) to remove that duplication.
To fill down, just right click on the column. There's menu for filldown, no need for formula.
View attachment 47278
I'm not sure what you are asking here...
= if Text.Start([Column1],2) = "AU" then [Index] else null
Ok, here's your issue.
There is no consistent structure in original data, based on "null" value.
I.E. lamborghini & mercedes are the only group with null in Column1.
So you need to base it on something else.
Looking at your data, one consistent thing is Column1 first row of each group.
It looks like it always starts with AU.
So you'd change first custom column's formula as below.
Code:= if Text.Start([Column1],2) = "AU" then [Index] else null
As well, you need more than just the one Text.Combine(). Since, there are multiple columns that has multiple rows of data. You need to do this operation for each of the column that has multiple rows of data.
See if you can apply the steps above. If you have specific issue that you encounter during the process, post here and I'll help.
= if [Column1] = null then null else if Value.Is(Value.FromText([Column1]), type number) then null else if Value.Is(Value.FromText(Text.Replace([Column1],Text.Start([Column1], 2),"")), type number) then [Index] else null
Then, you'd need to think of another logic to identify grouping.
Is it always going to be 2 letter followed by numeric? If so, you could use something like below. In place of previous custom column function.
Code:= if [Column1] = null then null else if Value.Is(Value.FromText([Column1]), type number) then null else if Value.Is(Value.FromText(Text.Replace([Column1],Text.Start([Column1], 2),"")), type number) then [Index] else null
This will create result like below, based on what's in [Column1]. And you can use filldown on this column.
View attachment 47657
Procedure and function to use will depend on your actual data set. If unable to come up with solution, upload more samples that represent your actual data set accurately.
1. "" is for Text.Replace(text,"old string to replace","new string to replace with").
So, I'm replacing starting 2 character identified with Text.Start(text,2) with blank. Equivalent to removing the first 2 character of string.
2. Like I said previously, without sample that accurately represent your actual data set. It's hard to give you solution that will work with your actual data set.