locked
Group data from XML file RRS feed

  • Question

  • I have some data that is pulled from a XML file.  Each section contains a Location and Acct Number.  Under the Acct number there additional repeating sections that contain the date and associated amount.  Once it gets into Power Query I'm not sure how to pivot the data onto the same line.

    Location = 1 G/L Asset Acct No = 1 2019-01-31 Date
    Location = 1 G/L Asset Acct No = 1 95.88 Amount
    Location = 1 G/L Asset Acct No = 1 2019-02-28 Date 
    Location = 1 G/L Asset Acct No = 1 95.91 Amount

    What I need

    Location = 1 G/L Asset Acct No = 1 2019-01-31 95.88
    Location = 1 G/L Asset Acct No = 1 2019-02-28 95.91

    Sample of the XML

    			<Group Level="3">
    				<Group Level="4">
    					<GroupFooter>
    						<Section SectionNumber="0">
    							<Field Name="Field85" FieldName="{@rcPeriodEndV}"><FormattedValue>01/31/2019</FormattedValue><Value>2019-01-31</Value></Field>
    							<Field Name="Field191" FieldName="{@rcPeriodExpenseV}"><FormattedValue>$  96</FormattedValue><Value>95.88</Value></Field>
    						</Section>
    					</GroupFooter>
    				</Group>
    			</Group>
    			<Group Level="3">
    				<Group Level="4">
    					<GroupFooter>
    						<Section SectionNumber="0">
    							<Field Name="Field85" FieldName="{@rcPeriodEndV}"><FormattedValue>02/28/2019</FormattedValue><Value>2019-02-28</Value></Field>
    							<Field Name="Field191" FieldName="{@rcPeriodExpenseV}"><FormattedValue>96</FormattedValue><Value>95.91</Value></Field>
    						</Section>
    					</GroupFooter>
    				</Group>
    			</Group>

    Thoughts?  Thanks!


    Friday, March 29, 2019 10:42 PM

Answers

  • You need a way to relate the rows in order to pivot the columns.

    Your table has no column names, so the example below uses Column1, Column2, etc.

    let
        ...
    previousStep = .., addedIndex = Table.AddIndexColumn(previousStep, "Index", 0, 1), transformedIndex = Table.TransformColumns(addedIndex, {"Index", each Number.IntegerDivide(_, 2)}), pivotedColumns = Table.Pivot(transformedIndex, List.Distinct(transformedIndex[Column4]), "Column4", "Column3"), removedIndex = Table.RemoveColumns(pivotedColumns,{"Index"}) in removedIndex

    In the above code:
    1) previousStep is your current last step that returns a table
    2) Column3 is the column with Date and Amount values
    3) Column4 is the column with 'Date' and Amount' text

    • Proposed as answer by Imke FeldmannMVP Sunday, March 31, 2019 9:51 AM
    • Marked as answer by st9912345 Tuesday, April 2, 2019 9:00 PM
    Saturday, March 30, 2019 2:10 AM

All replies

  • You need a way to relate the rows in order to pivot the columns.

    Your table has no column names, so the example below uses Column1, Column2, etc.

    let
        ...
    previousStep = .., addedIndex = Table.AddIndexColumn(previousStep, "Index", 0, 1), transformedIndex = Table.TransformColumns(addedIndex, {"Index", each Number.IntegerDivide(_, 2)}), pivotedColumns = Table.Pivot(transformedIndex, List.Distinct(transformedIndex[Column4]), "Column4", "Column3"), removedIndex = Table.RemoveColumns(pivotedColumns,{"Index"}) in removedIndex

    In the above code:
    1) previousStep is your current last step that returns a table
    2) Column3 is the column with Date and Amount values
    3) Column4 is the column with 'Date' and Amount' text

    • Proposed as answer by Imke FeldmannMVP Sunday, March 31, 2019 9:51 AM
    • Marked as answer by st9912345 Tuesday, April 2, 2019 9:00 PM
    Saturday, March 30, 2019 2:10 AM
  • Thanks. I'll review this today.
    Tuesday, April 2, 2019 5:17 PM
  • This is great.  It took my a bit to understand and also helps if I read your code correctly! 

    each Number.IntegerDivide(_, 2)})

    What does "_" mean? How is this function (Number.IntegerDivide) represented in the UI or is this only a Advanced Editor function only?

    This seams like the equivalent of power-shell ($_.) For each pipeline value, do this.


    Tuesday, April 2, 2019 9:03 PM
  • What does "_" mean? How is this function (Number.IntegerDivide) represented in the UI or is this only a Advanced Editor function only?

    This seams like the equivalent of power-shell ($_.) For each pipeline value, do this.

    Yes, the meanings are similar. See the following link for M documentation:

    https://docs.microsoft.com/en-us/powerquery-m/power-query-m-reference

    You can do the integer divide from the UI, since the UI supports some basic functions. I often forget that these functions are available in the UI, since a large percentage of the expressions I end up working with have to be coded manually. 

    Tuesday, April 2, 2019 10:18 PM