• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

how to use power query move columns

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.
Code:
= if [Column2] <> null then [Index] else null
4. Select the added custom column and "Fill Down"
5. Select the custom column and Group by -> All Rows (I named generated column [NewCol])
6. Add custom column with following formula
Code:
= Text.Combine([NewCol][Column1], ",")
7. Expand [NewCol] for Column2 only.
8. Group by on Custom.1 (combined text column in step 6), and max of Column2.
9. Split Custom.1 by delimiter.
upload_2017-10-31_9-42-28.png

10. Rename columns as desired.
Load result to desired location.
 

Attachments

  • example (4).xlsx
    17.9 KB · Views: 3
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.
Code:
= if [Column2] <> null then [Index] else null
4. Select the added custom column and "Fill Down"
5. Select the custom column and Group by -> All Rows (I named generated column [NewCol])
6. Add custom column with following formula
Code:
= Text.Combine([NewCol][Column1], ",")
7. Expand [NewCol] for Column2 only.
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.


Hi @Chihiro

Thanks for your help...

i got some question to ask you.

why need to added index column? and why added custom column will become 0,3 and null?

Thanks
 

Attachments

  • a.png
    a.png
    10.4 KB · Views: 1
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.
Code:
= if [Column2] <> null then [Index] else null
4. Select the added custom column and "Fill Down"
5. Select the custom column and Group by -> All Rows (I named generated column [NewCol])
6. Add custom column with following formula
Code:
= Text.Combine([NewCol][Column1], ",")
7. Expand [NewCol] for Column2 only.
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.

Hi @Chihiro

that formulas doesn't work for me.

i download from my system backend format is like this, pls refer attachment.

Thanks
 

Attachments

  • Example 2.xlsx
    13.5 KB · Views: 2
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.
 
As for your question, in order to manipulate data in row context, especially when there's nothing in data that identifies grouping, you will need to add that layer in PQ.

Index and subsequent custom column is used to identify which row contains info that should be combined into same row.
 
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.

Hi @Chihiro

i have upload 5set of data, Pls Refer attachment.

Thanks
 

Attachments

  • Example 2.xlsx
    16.1 KB · Views: 2
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.
Code:
= if [Column1] = null then null else [Index]
3. Fill down on added custom column.
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).
Code:
= Text.Combine([AllRow][Column4],"#(lf)")
6. Rename custom column above to Column4
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
Code:
= Table.SplitColumn(#"Filtered Rows", "Column2", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv))
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.

See attached.
 

Attachments

  • Example 2 (1).xlsx
    22.6 KB · Views: 3
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.
Code:
= if [Column1] = null then null else [Index]
3. Fill down on added custom column.
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).
Code:
= Text.Combine([AllRow][Column4],"#(lf)")
6. Rename custom column above to Column4
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
Code:
= Table.SplitColumn(#"Filtered Rows", "Column2", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv))
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.

See attached.

Hi @Chihiro

Thanks for your help, my problem is solve.

but i got question to ask...

why after Expanded ALLRow, will become Duplicate?

and what function for fill down? why after click will auto got number, and why is that number?

Thanks
 

Attachments

  • Capture.PNG
    Capture.PNG
    28.2 KB · Views: 12
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.
upload_2017-11-9_15-17-26.png



why after click will auto got number, and why is that number?
I'm not sure what you are asking here...
 
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...

Hi @Chihiro

Alright, thanks for your help
 
Hi @Chihiro

i still got 1 problem, this is i download another website, this is difference format, i tried use you give that formula is doesn't work. so need your help. Pls Refer attachment .

Thanks
 

Attachments

  • Example.xlsx
    15.9 KB · Views: 2
Please upload file with your attempt at implementation, then I can advise you on where you went wrong or how to accommodate for the change in structure.
 
Hi @Chihiro

when i Split Column, there is all null...Pls Refer attachment .

Thanks
 

Attachments

  • Example.xlsx
    24.5 KB · Views: 4
  • Example.png
    Example.png
    84.3 KB · Views: 2
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.
 
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.

Hi @Chihiro

first row of each group, if sometime start from AU or OU or maybe more than that? what CODE i have to change?

Thanks
 
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.
upload_2017-11-27_8-44-51.png

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.
 
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.

Hi @Chihiro

Not always going to be 2 letter followed by number, sometime is 3letter followed by number, sometime is full of letter. Pls Refer attachment

Thanks
 

Attachments

  • Example.png
    Example.png
    29.1 KB · Views: 16
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.
 
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.

Hi @Chihiro

alright....Thanks
 
Back
Top