+ Reply to Thread
Results 1 to 8 of 8

Data normalization in Excel - how to with certain example

  1. #1
    Forum Contributor
    Join Date
    01-16-2014
    Location
    Poland
    MS-Off Ver
    Excel 2016
    Posts
    1,798

    Data normalization in Excel - how to with certain example

    Hi Guys,

    i have data like here:

    Screenshot_37.png

    and i want to get:

    Screenshot_38.png

    How can i achieve this using pivot table? (no vba).

    Please help,
    Best,
    Jacek
    Attached Files Attached Files

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Seattle, WA, USA
    MS-Off Ver
    MS Office 365 Excel 2016
    Posts
    14,396

    Re: Data normalization in Excel - how to with certain example

    Hi Jacek,

    I'm not sure you can get your answer using Pivot Tables. It looks more like you want to UnPivot your data, but I'm not following your answer. When you UnPivot, you get more rows, which you have.

    See:
    https://support.office.com/en-us/art...5-f588221c7098
    One test is worth a thousand opinions.
    Click the * below to say thanks.

  3. #3
    Forum Contributor
    Join Date
    01-16-2014
    Location
    Poland
    MS-Off Ver
    Excel 2016
    Posts
    1,798

    Re: Data normalization in Excel - how to with certain example

    Hi Marvin,

    thank you.

    i want to get expected results look like in screen attached.
    Your method is not working.

    You can see Expected result (whaT I WANT to achevie) and unpivot table result...

    Best,
    Jacek
    Attached Files Attached Files

  4. #4
    Forum Contributor
    Join Date
    01-16-2014
    Location
    Poland
    MS-Off Ver
    Excel 2016
    Posts
    1,798

    Re: Data normalization in Excel - how to with certain example

    Anyone?

    Jacek

  5. #5
    Valued Forum Contributor
    Join Date
    09-25-2015
    Location
    Nowy Tomysl, Poland
    MS-Off Ver
    2019
    Posts
    386

    Re: Data normalization in Excel - how to with certain example

    Look at this solution (Power Query)
    Attached Files Attached Files

  6. #6
    Forum Contributor
    Join Date
    01-16-2014
    Location
    Poland
    MS-Off Ver
    Excel 2016
    Posts
    1,798

    Re: Data normalization in Excel - how to with certain example

    wow thank you,
    awesome!

    One question,
    in attachment please find screens.
    What step did you do to transform table from columns to row with this "Cancel moving other columns" step?

    Best,
    Jacek
    Attached Images Attached Images

  7. #7
    Valued Forum Contributor
    Join Date
    09-25-2015
    Location
    Nowy Tomysl, Poland
    MS-Off Ver
    2019
    Posts
    386

    Re: Data normalization in Excel - how to with certain example

    In the PQ settings, highlight the formula bar (you will see the syntax of the M language).
    If this is the case, it looks like this:
    "= Table.UnpivotOtherColumns(#"Nagłówki o podwyższonym poziomie", {"Function", "Server"}, "Atrybut", "Wartość")"
    That is, select the first two columns and the right mouse button on the selected columns - Cancel changing other columns

  8. #8
    Forum Contributor
    Join Date
    01-16-2014
    Location
    Poland
    MS-Off Ver
    Excel 2016
    Posts
    1,798

    Re: Data normalization in Excel - how to with certain example

    Thank you !

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1