Forum Discussion

plutoforever's avatar
plutoforever
Frequent Visitor
4 years ago
Solved

Need help with Matrix display

For the life of me I can't figure out a logical process to get what I need so trying here.  I have a table of data in the following format in BI (which is being generated by a SQL statement reading m...
Need Help
  • lbendlin's avatar
    lbendlin
    4 years ago

    That's a great idea.  If you drop the [Sum of Unit Price] column and do some index trickery you can shoehorn everything into one visual

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dY5RCsAgDEPvUtiflMa26s4i3v8aU+eYDPYR0kdLk1oJAhQKpGKpG7qis5fNhXWuWOQYuASGb/ivFippRkxib5Cb8XBl2HiDfsA5j2mGnp+0LdVmJ5E43R/EXbVsCD6x0Ki1Cw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Customer #" = _t, #"EF Ticket #" = _t, Units = _t, #"Sum of Unit Price" = _t, Subtotal = _t, #"Sales Tax" = _t, #"Sales Tax %" = _t, #"Federal Excise Tax" = _t, #"Federal Excise Tax Rate" = _t, #"State Road Tax" = _t, #"State Road Tax Rate" = _t, #"Federal Oil Spill Fee" = _t, #"Federal Oil Spill Fee Rate" = _t, #"Federal LUST Fee" = _t, #"Federal LUST Fee Rate" = _t, #"State Agriculture Inspection Fee" = _t, #"State Agriculture Inspection Fee Rate" = _t, #"State Transport Load Fee" = _t, #"State Transport Load Fee Rate" = _t]),
    #"Renamed Columns" = Table.RenameColumns(Source,{{"Sales Tax %", "Sales Tax Rate"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Sum of Unit Price", Currency.Type}, {"Units", type number}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"Customer #", "EF Ticket #", "Units", "Subtotal", "Sales Tax", "Sales Tax Rate", "Federal Excise Tax", "Federal Excise Tax Rate", "State Road Tax", "State Road Tax Rate", "Federal Oil Spill Fee", "Federal Oil Spill Fee Rate", "Federal LUST Fee", "Federal LUST Fee Rate", "State Agriculture Inspection Fee", "State Agriculture Inspection Fee Rate", "State Transport Load Fee", "State Transport Load Fee Rate"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Other Columns", {"Customer #", "EF Ticket #"}, "Attribute", "Value"),
    #"Added Index" = Table.AddIndexColumn(#"Unpivoted Other Columns", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Rate", each if Number.IsEven([Index]) then #"Added Index"{[Index]+1}[Value] else null),
    #"Replaced Value" = Table.ReplaceValue(#"Added Custom",each [Index],each Number.Mod([Index]+14,16),Replacer.ReplaceValue,{"Index"}),
    #"Filtered Rows" = Table.SelectRows(#"Replaced Value", each [Index]>13 or [Rate] <> null),
    #"Replaced Value1" = Table.ReplaceValue(#"Filtered Rows",each [Rate],each if [Index]=14 then null else [Rate],Replacer.ReplaceValue,{"Rate"})
in
    #"Replaced Value1"

     

     

lbendlin's avatar
lbendlin
Super User
4 years ago

You need to massively change the structure of your source data. Note that each column needs to have the same format so your Rates column has to either be all percentages, all decimals, or all text.  You also need to rename "Sales Tax %"  to "Sales Tax Rate"  to make this work.

 

(out of curiosity - how can you have fractions of units?)

 

 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dY5RCsAgDEPvUtiflMa26s4i3v8aU+eYDPYR0kdLk1oJAhQKpGKpG7qis5fNhXWuWOQYuASGb/ivFippRkxib5Cb8XBl2HiDfsA5j2mGnp+0LdVmJ5E43R/EXbVsCD6x0Ki1Cw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Customer #" = _t, #"EF Ticket #" = _t, Units = _t, #"Sum of Unit Price" = _t, Subtotal = _t, #"Sales Tax" = _t, #"Sales Tax %" = _t, #"Federal Excise Tax" = _t, #"Federal Excise Tax Rate" = _t, #"State Road Tax" = _t, #"State Road Tax Rate" = _t, #"Federal Oil Spill Fee" = _t, #"Federal Oil Spill Fee Rate" = _t, #"Federal LUST Fee" = _t, #"Federal LUST Fee Rate" = _t, #"State Agriculture Inspection Fee" = _t, #"State Agriculture Inspection Fee Rate" = _t, #"State Transport Load Fee" = _t, #"State Transport Load Fee Rate" = _t]),
    #"Renamed Columns" = Table.RenameColumns(Source,{{"Sales Tax %", "Sales Tax Rate"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Sum of Unit Price", Currency.Type}, {"Subtotal", Currency.Type}, {"Units", type number}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Customer #", "EF Ticket #", "Units", "Sum of Unit Price", "Subtotal"}, "Attribute", "Value"),
    #"Added Index" = Table.AddIndexColumn(#"Unpivoted Other Columns", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Rate", each if Number.IsEven([Index]) then #"Added Index"{[Index]+1}[Value] else null),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Rate] <> null))
in
    #"Filtered Rows"

 

 

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".

 

This would then yield a display like this (note that everything is formatted as text)

 

You can then filter out all the rows where the value is 0

 

but you can see that the sort order is off AND your summary rows are missing.   The first issue can be resolved by adding a dedicated sort column to the Power Query code.  But the second issue can only be solved by using a separate visual, or by - again - redesigning your data model and separating out the header information into a  parent fact table.