1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. 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...

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

Discussion in 'Power Pivot, Power Map etc' started by Oscarr, Oct 31, 2017.

  1. Oscarr

    Oscarr Member

    Messages:
    50
    Hi

    anyone know how to use power query to move columns? pls refer attachment.

    Bro @rahulshewale1, do you know?

    Thanks

    Attached Files:

  2. Chihiro

    Chihiro Excel Ninja

    Messages:
    3,908
    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 (vb):
    = 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 (vb):
    = 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.

    Attached Files:

    Oscarr and Thomas Kuriakose like this.
  3. Oscarr

    Oscarr Member

    Messages:
    50

    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

    Attached Files:

    • a.png
      a.png
      File size:
      10.4 KB
      Views:
      1
  4. Oscarr

    Oscarr Member

    Messages:
    50
    Hi @Chihiro

    that formulas doesn't work for me.

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

    Thanks

    Attached Files:

  5. Chihiro

    Chihiro Excel Ninja

    Messages:
    3,908
    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.
    Oscarr likes this.
  6. Chihiro

    Chihiro Excel Ninja

    Messages:
    3,908
    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.
  7. Oscarr

    Oscarr Member

    Messages:
    50
    Hi @Chihiro

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

    Thanks

    Attached Files:

  8. Chihiro

    Chihiro Excel Ninja

    Messages:
    3,908
    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 (vb):
    = 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 (vb):
    = 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 (vb):
    = 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.

    Attached Files:

    Oscarr and Thomas Kuriakose like this.
  9. Oscarr

    Oscarr Member

    Messages:
    50
    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

    Attached Files:

  10. Chihiro

    Chihiro Excel Ninja

    Messages:
    3,908
    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



    I'm not sure what you are asking here...
    Oscarr likes this.
  11. Oscarr

    Oscarr Member

    Messages:
    50
    Hi @Chihiro

    Alright, thanks for your help
  12. Oscarr

    Oscarr Member

    Messages:
    50
    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

    Attached Files:

  13. Chihiro

    Chihiro Excel Ninja

    Messages:
    3,908
    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.
  14. Oscarr

    Oscarr Member

    Messages:
    50
    Hi @Chihiro

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

    Thanks

    Attached Files:

  15. Chihiro

    Chihiro Excel Ninja

    Messages:
    3,908
    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 (vb):
    = 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.
    Oscarr likes this.
  16. Oscarr

    Oscarr Member

    Messages:
    50
    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
  17. Chihiro

    Chihiro Excel Ninja

    Messages:
    3,908
    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 (vb):
    = 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.
    Oscarr likes this.
  18. Oscarr

    Oscarr Member

    Messages:
    50
    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

    Attached Files:

  19. Chihiro

    Chihiro Excel Ninja

    Messages:
    3,908
    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.
    Oscarr likes this.
  20. Oscarr

    Oscarr Member

    Messages:
    50
    Hi @Chihiro

    alright....Thanks

Share This Page