Sort Tags in Query - (mcode?)

[tonijj]

Hi,

I have a challenge with some external data that I’d hope that the BI query would help me with.

As the data is external – there is no way for me to do anything about the source data.

Here is an example table of my Source Data:

 

Here is how the result actually is if I split by delimiter (;)

 

Here is how I would like the end result to be:

 

I guess I have to create master tables to use Lookup in sort of way? E.g. create custom columns? I would be able to use that with DAX, but rather try to learn from you guys in Mcode and query in the backend. 

Secondly, one challenge would be that some suppliers have several Industry codes, and they need to be separated. Im all fine that it takes the first Industry code and moves it to "Industry code 1" and then takes the second one to the corresponding column, but, by looking at the result example, the data is not in sync.

Really appreciate any help here, and would be happy to learn How to so I know for the future, and maybe so that it can help other ppl in the future. 

Update: Files attached.

Documents here 

DROPBOX Link  

[v-yalanwu-msft · Accepted solution]

Hi, @tonijj ;

Sorry for the late reply. If you have new needs, you can open a new post, there are professional engineers and enthusiastic netizens will help you! Thank you for your support！

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[tonijj]

@v-yalanwu-msft The need is still the same 🙂 

Its "just" the last step that I need help with. Think u could help with that last part as described in my last post? 

[v-yalanwu-msft]

Hi, @tonijj ;

You could change the custom column.

List.First( List.Select([Custom],each
 Text.Contains(_,"Omega")  or
 Text.Contains(_,"Zeta") or
 Text.Contains(_,"Beta") or
 Text.Contains(_,"AIpha") or
 Text.Contains(_,"Business Unit") 
 
 ))

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Zc5NC4MwDAbgvxJ6Vmjd8OJJx2AexgbiSTx0GrXgqqQtY/9+2rEvdk3ePG+qiqXzPCILmIg5hxAOktqbJEwgc0ZpNAZKrSxkaGUCIhY8LKbOPiN73UJpkNZzVgcVO6qGJrPsPShW0LzTv+Dpir0XOQ8/pV9i5MXM0QWJ7ivIl3LYjZMdlO4TSPN5kMt844PFpH0ojtbWM5KaByQ5mgRy3ZE0llxj3f8jL2bL6voB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Supplier = _t, Tags = _t, #"Supplier Number" = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each Text.Split([Tags],";")),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Industry Code 1", each List.First([Custom])),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Industry Code 2", each List.First(List.Skip(List.Select([Custom],each Text.At(Text.Trim(_),0)<="9"),1))),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Business Unit", each List.First( List.Select([Custom],each
 Text.Contains(_,"Omega")  or
 Text.Contains(_,"Zeta") or
 Text.Contains(_,"Beta") or
 Text.Contains(_,"AIpha") or
 Text.Contains(_,"Business Unit") 
 
 ))),
    #"Added Custom4" = Table.AddColumn(#"Added Custom3", "Service Tower", each List.First(List.Select([Custom],each not Text.Contains(_,"Business Unit") and Text.At(Text.Trim(_),0)>"9"))),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom4",{"Supplier", "Supplier Number", "Industry Code 1", "Industry Code 2", "Business Unit", "Service Tower"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Other Columns",{"Supplier Number", "Supplier", "Industry Code 1", "Industry Code 2", "Business Unit", "Service Tower"}),
    #"Replaced Value" = Table.ReplaceValue(#"Reordered Columns"," Business Unit ","",Replacer.ReplaceText,{"Business Unit"})
in
    #"Replaced Value"

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[tonijj]

@v-yalanwu-msft 

Thanks! In the process of trying now.

So, that works with a smaller number of sets I'd say, but if the corresponding table has e.g. 100 records, it would make more sense to use a "Lookupvalue" thinking? Is that possible? 

[v-yalanwu-msft]

Hi, @tonijj ;

Since I can't reproduce your third picture, pleaseshare a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[tonijj]

Hi  @v-yalanwu-msft @lbendlin 

Now uplodaded via OneDrive. 
Two files

1. mockup pbix 

2. excel with source data, mockup and cleaned, but it should illustrate the principal of what I want to achieve

[lbendlin]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Zc7NCoMwEATgV1lyVoht8eJJS6EeSgviSTykumrAxrBJKH37akr/zzvzzVYVS7UekQUsijmHEPaC2qsgTCBzRio0BkolLWRoRQJRHPGwmDr7iOxUC6VBWuqsDip2kA1NZr57MFpA80p/g8cL9l7kPHyPfogrL2aOzkh0W0A+j8N2nOwgVf/rpbkexJxa+1oxKV+JV8sPJySpByQxmgRy1ZEwllxj3f9bT2bD6voO", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Supplier = _t, Tags = _t, #"Supplier Number" = _t]),
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "Tags", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Industry Code 1", "Business Unit", "Industry Code 2", "Service Tower"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Split Column by Delimiter",{"Supplier Number", "Supplier", "Industry Code 1", "Industry Code 2", "Business Unit", "Service Tower"}),
    #"Replaced Value" = Table.ReplaceValue(#"Reordered Columns"," Business Unit ","",Replacer.ReplaceText,{"Business Unit"})
in
    #"Replaced Value"

Note:  Reordering columns in Power Query is not really necessary. Leave that for the presentation layer.

[tonijj]

@lbendlin Regarding reordering columns, fully agree. 

Here its more about "adding the Tag information into the correct bucket". 

Im in the process of reinstalling my computer, incl the VM. Any chance you could attach the pbix file in the meantime, then I can have a look at that, and hopefullt re-engineer what you did so I can learn as well 🙂 

[tonijj]

@lbendlin Thanks!

But it didnt solve the issue, pardon my drawing disabilities here 🙂 but the data is not in the correct column

Row4 = Bus Unit Alpa should be under column "Business Unit" and "Infrastructure" under "Service Tower" column..  

[lbendlin]

You will need to redefine your rules.

1. Industry Code 1:  First entry starting with a number

2. Industry Code 2: second entry starting with a number

3. Business Unit: Entry starting with Business Unit

4. Service Tower: Anything else

Please confirm.

[tonijj]

Confirm that the rules you mentioned would work! 

Then we're just back to that I dont know how to write them. 

If you look at the attached excel (previous post) there is a tab for Business Units. 

So, a rule could be to do a lookup, IF the value is found in "business unit table", insert the tag value in the column "Business Unit". 

[tonijj]

@lbendlin Thanks! Now that file seem to have most of it down! 
@v-yalanwu-msft 

Last question, hope you dont mind, and I do really appreciate the help here, truly! 

Is it possible to incorporate a Lookupvalue function?

Example: Business Unit

Source data does not include the wording "Business Unit" as an identifier, but only "Alpha, Beta, Omega and Zeta". 

As there is a table (Business Unit Table) in the data, also in the excel example file. 

Is it possible for the custom step for sorting the Business Unit to make the code to, and in laymans terms now:

Lookup values In "Business Unit Table", and if there is a match, return the value from the Tags column into the new Custom column. 

[lbendlin]

Please provide sanitized sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

[v-yalanwu-msft]

Hi, @tonijj ;

I tested it.

1. split column by ";".

Original table.

2.replace value.

The final show:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Zc7NCoMwEATgV1lyVoht8eJJS6EeSgviSTykumrAxrBJKH37akr/zzvzzVYVS7UekQUsijmHEPaC2qsgTCBzRio0BkolLWRoRQJRHPGwmDr7iOxUC6VBWuqsDip2kA1NZr57MFpA80p/g8cL9l7kPHyPfogrL2aOzkh0W0A+j8N2nOwgVf/rpbkexJxa+1oxKV+JV8sPJySpByQxmgRy1ZEwllxj3f9bT2bD6voO", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Supplier = _t, Tags = _t, #"Supplier Number" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Supplier", type text}, {"Tags", type text}, {"Supplier Number", Int64.Type}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Changed Type",{"Supplier Number", "Supplier", "Tags"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Reordered Columns", "Tags", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Tags.1", "Tags.2", "Tags.3", "Tags.4"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Tags.1", type text}, {"Tags.2", type text}, {"Tags.3", type text}, {"Tags.4", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type1"," Business Unit ","",Replacer.ReplaceText,{"Tags.2"})
in
    #"Replaced Value"

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[tonijj]

Hi @v-yalanwu-msft 

That didnt solve the problem as it doesnt reorder the data correctly. What your file does is that it results in my 2nd picture, whereas I want it to be as the 3rd picture. Thats the tricky part 😕 

[lbendlin]

Please provide sample data in a usable format, not as a screenshot.