Power Query Pivot Multiple Rows / Columns Dynamically

[MPR]

Hello,

My source is a spreadsheet that has data organized so that one records has multiple rows.  It may have 4 rows or 5, depending on what fields are populated.  Following is a sample of what the data looks like:

I am killing myself trying to figure out how to transform/flatten the data so each record is on one row.  Following is a snapshort of how I would like it to end up.  Hopefully its not too small to see.

I have tried everything I can think of relating to pivoting the data but I can't get there.  At first, I tried creating a column assigning a number 0, 1, 2, or 3 that I could then pivot but as you can see in the sample, occassionally a record has 5 rows.  If there was a way to create a column that starts counting and resets everytime it comes to the Set Up Balance row, I may be able to get it to work.  

Hopefully, I am making sense.  I may be thinking about it all wrong.  Any help in pivoting the data is greatly appreciated.

Thanks,

MPR

[v-lili6-msft]

hi, @MPR

I have tested on your screenshots, I'm afraid it difficult to achieve in Power Query. For your basic table is a little confusing,

there are no column headings or classification conditions for each row.

You may try to pivot it in other tools, It is best to give the raw data a standard format.

Best Regards,

Lin

[MPR]

Hello Lin,

Unfortunetly, the format I gave in the example is how I receive it and I have no control over the source data.  It is from a 3rd party.

I think it could be done if there is a way to set up an number column that resets to 0 everytime it comes to a row that contains "Set Up" in the balance column.  The format is such that the "Set Up" row is always the first row of a new record.  

Any ideas how to set up a number column that resets when the Balance column contains "Set Up"?

Thanks,

Mark

[v-lili6-msft]

hi, @MPR

I have kept test on it. Maybe this can help you,

After entering an index column for each name, I divide this query into four queries.

Then Pivot each query and then merge them by name.

Note: you need to rename the column name, for it's not in the original data. 

See more details in my pbix file.

Best Regards,
Lin

[v-lili6-msft]

hi, @MPR

For " Any ideas how to set up a number column that resets when the Balance column contains "Set Up" " you could try these steps as below:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("tVDBSsQwEP2VIXhYId2m6Ua3PVlFEakXi3goe8iGsBtsEmmyF7/eKe6W6HoVMgy8N/PmvfQ9efJ7F7yjUyeUfL9OR3j9qOGCLRkjG9qTmXrR6mCN26Ukndmfs41S/uBiOtpZE/f01gwD8o/eaqih4GW2ElfZ9bpiiD6YMUQoipxXOWfFOvVTcnFSwi14lsZBF3HizY/vKCWEyI4lEG3lpMRzVp6U/rLf+gCN2+lBBwp3zeRnJc4i3Vtphvp3sMQQ4nKrbrCWyttkuZVj0I4C9s859QKvXMIxdvWv3775Ag==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Name = _t, #"Contact Information" = _t, #"Meeting Date" = _t, Balances = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Contact Information", type text}, {"Meeting Date", type text}, {"Balances", type text}}),
    #"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Custom", each if Text.Contains([Balances], "Set Up") then [Name] else null),
    #"Filled Down" = Table.FillDown(#"Added Conditional Column",{"Custom"}),
    Partition = Table.Group(#"Filled Down", {"Custom"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}),
    #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"Name", "Contact Information", "Meeting Date", "Balances", "Custom", "Index"}, {"Name", "Contact Information", "Meeting Date", "Balances", "Custom.1", "Index"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Partition",{"Custom.1"})
in
    #"Removed Columns"

Result:

hope it can help you.

Best Regards,
Lin