Re-Organize Data Matrix

[Tom2]

Dear Excel Experts,

I'm trying to figure out how to use formula to re-organize data column but without luck. A sample data and the criteria is attached. Any help will be greatly appreciated.



Tom

 

[p45cal]

In the attached, the green tables are the result of Power Query queries on the original data. They more or less agree with yours, and where they don't I think the green tables are right.
As I've left the sheet, the green tables have been sorted by me moving rows around manually so that it's easy to compare your expected results with them. The minute they're refreshed (by right-clicking a green table and choosing Refresh) they'll revert to their default order (the result can be sorted by Power Query too but I'm not sure what order you want them in).

 

[Tom2]

In the attached, the green tables are the result of Power Query queries on the original data. They more or less agree with yours, and where they don't I think the green tables are right.
As I've left the sheet, the green tables have been sorted by me moving rows around manually so that it's easy to compare your expected results with them. The minute they're refreshed (by right-clicking a green table and choosing Refresh) they'll revert to their default order (the result can be sorted by Power Query too but I'm not sure what order you want them in).

Thank p45cal so much!

I'm not very familiar with Power Query. So can you please explain a little bit more details about the procedures of how to use Power Query to generate the expected results?

Also, is that possible to use formula to generate the outcomes?

Best,
Tom

 

[AlanSidman]

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.

 

[p45cal]

Also, is that possible to use formula to generate the outcomes?

I suspect it might be but they would be long and complicated; getting consecutive/same dates together would not be trivial! I'm not going to try.

 

[Tom2]

I suspect it might be but they would be long and complicated; getting consecutive/same dates together would not be trivial! I'm not going to try.

Thank p45cal!

I'm trying to understand the following codes you created in power query. Can you please explain those codes further more? How they work to sort out the consecutive service date and merge them together? Thanks a lots!

-----------------------------------------------
= (tbl)=>
let
Source = tbl,//#"Table1 (2)",
#"Sorted Rows" = Table.Sort(Source,{{"Start date", Order.Ascending}}),
#"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1, Int64.Type),
#"Added Custom" = Table.AddColumn(#"Added Index", "ConsecDates", each Number.From([Start date])-[Index]),
#"Grouped Rows" = Table.Group(#"Added Custom", {"ConsecDates"}, {{"grp", each _, type table [Provider=nullable text, Service=nullable text, Customer=nullable text, Start date=nullable date, spending=nullable number, Index=number, ConsecDates=number]}, {"Start", each List.Min([Start date]), type nullable date}, {"End", each List.Max([Start date]), type nullable date}, {"Sum", each List.Sum([spending]), type nullable number}}),
#"Expanded grp" = Table.ExpandTableColumn(#"Grouped Rows", "grp", {"Provider", "Customer"}, {"Provider", "Customer"}),
#"Removed Duplicates" = Table.Distinct(#"Expanded grp"),
#"Removed Columns" = Table.RemoveColumns(#"Removed Duplicates",{"ConsecDates"})
in
#"Removed Columns"
----------------------------------------------

 

[p45cal]

That code is just a normal query converted to a function. In order to see what it does you need to convert it back into a normal query:
Go into the Power Query editor and expand the Queries pane on the left.
First of all duplicate fnStuff (right-click, Duplicate), the duplicate might end up being named fnStuff (2). Go into advanced editor and change the first lines of fnStuff (2) from:

(tbl)=>
let
    Source = tbl,//#"Table1 (2)",

to:

let
    Source = #"Table1 (2)",

(delete the first line altogether and edit the 3rd)
Click Done.
It'll tell you that the name Table (2) isn't recognised so we need to go about making one:
Duplicate Transfer#1, the duplicate might be named Transfer#1 (2).
Go into to edit normally (not advanced edit) this duplicate query, and choose the Grouped Rows step then click on the word Table on the first row:

You'll get a warning about replacing subsequent steps, click on Continue (it's only a duplicate after all). Rename this query from Transfer#1 (2) to Table1 (2) by right-clicking it and choosing Rename.:


It should look like:


whereupon fnStuff (2) should no longer show a warning.
Click on it and you'll see the steps on the right hand pane. This will allow you to see what each line of code does as you select each step.