Forum Discussion
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...
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"
You need to unpivot alll value columns into one Column
and then load data to data model and then you can get result the way you want!
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"
Thank you both that worked perfectly:
And to answer your question on units in most cases they are in gallons so a fraction would be part of a gallon filled
