Forum Discussion

MikekSSL's avatar
MikekSSL
Frequent Visitor
8 years ago

HierarchyID display

Hello I have a table with a standard Microsoft hierarchyID column positioning parts with a product Bill of Materials. I can display the BoM parts in Excel tabbomng across and back for each level us...
Need Help
MikekSSL's avatar
MikekSSL
Frequent Visitor
8 years ago

To explain how I am currently displaying is to use my Excel example

I use the data you created in the final table (either through a direct query or a stored proc ) but esentially comes down to looking like a csv file into PowerQry and use the colum splitter function on the single column which contains all my tab characters to push the components out to a new column compatible with their hierarchy depth

 

A snippet of my code in PowerQry

 

let
PartNo = Excel.CurrentWorkbook(){[Name="Partparam"]}[Content], <--(comment - this allows me to select which block of data we are displaying - not relevant for this discussion
Source = Sql.Database("uksqldemo", "Adventureworks2014", [Query="exec dbo.TabbedList

@Partno = " & "'" & PartNo[Sparams]{0} & "'" ]

)

,
#"Split Column by Delimiter" = Table.SplitColumn(Source, "listing", Splitter.SplitTextByDelimiter("#(tab)", QuoteStyle.Csv), {"listing.1", "listing.2", "listing.3", "listing.4", "listing.5", "listing.6"}),
#"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"listing.1", type text}, {"listing.2", type text}, {"listing.3", type text}, {"listing.4", type text}, {"listing.5", type text}, {"listing.6", type text}}),

 

 

 

The result after splitting the column is as follows.  Which is eaxctly how we need it.  Note the M or F is the gender column I have added for further modifications.

 

 

 

My ideal would be for vertical and horizontal lines joining up the parts so people can follow the levels and even better drill through the details of, in this case, the login info.

 

If we could do this on one or two drag and drops in PowerBI we would be v happy and I reckon another useful tool for PBI

 

Thanks

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
8 years ago

Hmm, I think what you really want to do is to fill in all of your values instead of having nulls. Take a look at the table created by this query:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wci9KTVfSUXLKzMkBUsGlVZVAyqc0GUR5JSZn6ybq5uQXpCrF6hBQm5mblE9YVX5GXl6lglN+JUGlAaklqUUEVbkk5hFU40uEu1KLizNRPOlclFhVqeCRX1ScClKdkZqTAzYrNScxLxMk5FKalkaSBo/8XFQP4dDhlJ8EcSYoPGMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [tier1 = _t, tier2 = _t, tier3 = _t, tier4 = _t, tier5 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"tier1", type text}, {"tier2", type text}, {"tier3", type text}, {"tier4", type text}, {"tier5", type text}}),
in
    #"Changed Type"

Once you have that, you can just build a hierarchy by draggin tier2 into tier1 and then tier3, tier4 and tier5. Then, if you use that hierarchy in a matrix, you get a really nice way to drill down and up into the hierarchy if you use the expanding drill down (branching down arrows).