question

Cherry-1813 avatar image
0 Votes"
Cherry-1813 asked ·

How to create pivot table with multiple headers in power query

Hi, I have a problem to create pivot table with multiple headers in power query, here is my base data: ![73603-base-data.jpg][1] And, here is my expected output: ![73604-expected-output.jpg][2] How can i generate the expected output? Thanks a lot! [1]: /answers/storage/attachments/73603-base-data.jpg [2]: /answers/storage/attachments/73604-expected-output.jpg

power-query-m
base-data.jpg (138.1 KiB)
expected-output.jpg (63.3 KiB)
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Lz-3068 avatar image
0 Votes"
Lz-3068 answered ·

Hi @Cherry-1813

(next time please upload & share a workbook - i.e. with OneDrive - as creating dummy data takes time. Thanks)

The below solution involves Power Pivot in addition to Power Query. Assuming Excel SourceTable

73670-excelsourcetable.png

NOTE: It seems you're not interested by the orange rows (no [Shift Start Time]). If I misunderstood this, adjust line #10 (step FilteredOutNullShiftStart) in the below Power Query code:
EDIT: I forgot a point > If you always have at least 1 record where fields [Shift], [Late In] & [Early Out] are not null then steps ReplacedNulls and RestoredNulls are not required

 // SourceTable
 let
     Source = Excel.CurrentWorkbook(){[Name="SourceTable"]}[Content],
     RequiredColumns = Table.SelectColumns(Source,
         {"Call-sign", "Date", "Shift", "Shift Start Time", "Late In", "Early Out"}
     ),
     ChangedTypes = Table.TransformColumnTypes(RequiredColumns,
         {<!-- -->{"Call-sign", type text}, {"Shift", type text}, {"Date", type date}}
     ),
     FilteredOutNullShiftStart = Table.SelectRows(ChangedTypes, each
         ([Shift Start Time] <> null)
     ),
     RemovedShiftStartTime = Table.RemoveColumns(FilteredOutNullShiftStart,{"Shift Start Time"}),
     ReplacedNulls = Table.ReplaceValue(RemovedShiftStartTime, null, "_Fake_Value_",
         Replacer.ReplaceValue,{"Late In", "Early Out"}
     ),
     UnpivotedColumns = Table.UnpivotOtherColumns(ReplacedNulls,
         {"Call-sign", "Date"}, "ColumnName", "Value"
     ),
     RestoredNulls = Table.ReplaceValue(UnpivotedColumns, "_Fake_Value_", null,
         Replacer.ReplaceValue,{"Value"}
     ),
     TimeValueToText = Table.AddColumn(RestoredNulls, "TextValue", each
         try Time.ToText(Time.From([Value]),"hh:mm:ss") otherwise [Value],
         type text
     ),
     RemovedValue = Table.RemoveColumns(TimeValueToText,{"Value"})
 in
     RemovedValue

Load this query as a Connection Only + Add this data to the Data Model:

73716-connectiononlydatamodel.png

Within Excel go to the Power Pivot tab > Measures > New Measure... and create measure ValueToReport with DAX formula:

    =CONCATENATEX(SourceTable,SourceTable[TextValue],", ")

Then create a Pivot Table from the Data Model with
[Call-sign] to Rows
[Date] & [ColumnName] to Columns
fxValueToReport to Values

Manually sort [ColumnName] (Shift, Late In, Early Out)
Remove Grand Total for Rows & Columns

73793-pivotfromdatamodel.png

Corresponding sample workbook avail. here






·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Cherry-1813 avatar image
0 Votes"
Cherry-1813 answered ·

Thanks for your suggestion, however, our office is using standard version of Excel, cannot support Power pivot.

Any other suggestion for me? Thanks.

· 1 ·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @Cherry-1813
a) What exact version of Excel do your office run? AFAIK all versions >= 2013 natively have/support Power Pivot
b) Doing something similar with Power Query only is probably doable (complex though) and will necessarily be slower than the above proposal. Before I think about it could you give an indication re. the number of rows that would be involved in the source table?

0 Votes 0 ·
Cherry-1813 avatar image
0 Votes"
Cherry-1813 answered ·

We are using standard 2013.

· 4 ·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

And Power Pivot isn't available in this version ??? Or is it simply that the Power Pivot COM Add-in hasn't been activated?

0 Votes 0 ·

After checking, we are using standard version 2013 and professional version 2013 has Power Pivot.

0 Votes 0 ·

@Cherry-1813. OK. I looked at alternatives in the meantime and there's an easy and efficient one...

From a business standpoint is there really a difference between "No Record" and a null value?
Asking as if - within PQ - we replace all text values with null so only num/time values remain, a Pivot Table will do the job, exception being that Shift won't be reported horizontaly (next to Late In/Early Out) but vertically:

74651-demo.png

Would this be OK?

0 Votes 0 ·
demo.png (35.1 KiB)
Show more comments
Cherry-1813 avatar image
0 Votes"
Cherry-1813 answered ·

I need to process around 2500 records.

·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.