+ Reply to Thread
Results 1 to 4 of 4

PowerQuery - beginner - how to get this view from this one?

  1. #1
    Registered User
    Join Date
    07-19-2021
    Location
    Poland
    MS-Off Ver
    2019
    Posts
    2

    PowerQuery - beginner - how to get this view from this one?

    Hello All,

    I am beginner in PowerQuery, but I'm trying to make some reports from XML files. I almost achieved my goals, but there is still one thing I cannot solve.

    In my XML there is Attribute: A00 (duty) and B00 (tax), assigned to given item. In order not to duplicate the value of the invoice, I have to either put filters on the report to show only A00 or only B00, otherwise when there's no filters on that column, I'd have doplicated a lot of things.

    I needed to use pivots, but once I did it, I have issue like on below table. Unfortunately I am not authorized yet to attach anything

    Eventually, I need changelayout of my report from the first table to the second table of the screenshot.
    Sorry for uploading photo on external server, but I wasn't able to attach anything in this thread.

    For any advices I'd be very grateful. Please keep in mind, that I'm "working" in excel since few weeks, I'm really beginner.

    MRN Invoice value Currency Code ROE Kind of Tax TAX Base Rate Amount of A00 Amount of B00
    20PL322080I0483690 116415,55 USD 44440000 3,9556 A00 433715 4,5 19517 null
    20PL322080I0483690 116415,55 USD 44440000 3,9556 B00 464980 23 null 106945
    20PL322080I0483690 116415,55 USD 55550000 3,9556 A00 69635 4,5 3134 null
    20PL322080I0483690 116415,55 USD 55550000 3,9556 B00 74739 23 null 17190





    MRN Invoice value Currency ROE Code Duty base VAT Base Duty VAT
    20PL322080I0483690 116415,55 USD 3,9556 44440000 433715 464980 19517 106945
    20PL322080I0483690 116415,55 USD 3,9556 55550000 69635 74739 3134 17190

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,374

    Re: PowerQuery - beginner - how to get this view from this one?

    Welcome to the forum.

    There are instructions at the top of the page explaining how to attach your sample workbook.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Registered User
    Join Date
    07-19-2021
    Location
    Poland
    MS-Off Ver
    2019
    Posts
    2

    Re: PowerQuery - beginner - how to get this view from this one?

    Hello Ali,

    I've added my excel with PowerQuery and one ZIP file where there's XML file from which PowerQuery takes the data. In the excel, there is also a sheet with desired layout of my report.
    I have add these attachments through "Additional Options" -> "Attachments" -> "Manage Attachments". Hope it will work now.
    Last edited by iLuminary; 07-22-2021 at 05:03 AM.

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: PowerQuery - beginner - how to get this view from this one?

    Not sure why the attachment would not work other than the button below the Quick Reply window is marked "Go Advanced" and not "Additional Options".
    That said the following is a formula based proposal to produce the output I believe you are looking for.
    1. The columns in yellow are populated using formulas similar to: =IF($F2="A00",$G2,"")
    2. The columns in green are populated using formulas similar to: =IFERROR(INDEX(A$2:A$5,AGGREGATE(15,6,(ROW(A$2:A$5)-ROW(A$1))/($N$2:$N$5<>""),ROWS($A$1:$A1))),"")
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 3
    Last Post: 03-25-2021, 07:39 PM
  2. PowerQuery in Excel: Use an Excel table to populate SQL table in PowerQuery
    By joeyslaptop in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 10-25-2019, 02:33 PM
  3. Return to worksheet from backstage view, i.e. close backstage view or exit backstage view
    By freddyroosevelt in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-18-2018, 12:44 AM
  4. Word Opens in DRAFT VIEW. How to open in PRINT LAYOUT view
    By Jenn68 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-28-2017, 06:47 PM
  5. Replies: 3
    Last Post: 08-31-2012, 12:40 PM
  6. Replies: 2
    Last Post: 05-24-2012, 05:02 PM
  7. [SOLVED] Issue - replies/view count labels in New Posts view
    By teylyn in forum Suggestions for Improvement
    Replies: 5
    Last Post: 03-16-2012, 05:45 AM

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