Need to map Category and SubCategory in another set of Data

[Sabareeshwari]

I have three excel named Mappings, Report 1 and Report2.

Will Import these excels and append the data from Report1 and Report2 and generate new table. in that table i need show category and subcategory based on Mappings and Report Table.

Shared Excel Datas for your Reference.

Report 1:

Category	Sub Category
Door related services - Student Room	Card lost
Common Area services	-
Door related services - Student Room	Others
Common Area services	-
Door related services - Student Room	Others
Door related services - Student Room	Card is not working
Door related services - Student Room	Card lost

Mapping Table:

CHC Service	Issues	Dropdowns	E-Facility Category	E-Facility Sub Category
Door Related Services - Student Room
	Type of Issue	Card is not working	Carpentry	Lock and Keys
		Door Lock is not working	Carpentry	Door Repairs
		Card lost	Carpentry	Lock and Keys
		Card broken	Carpentry	Lock and Keys
		Others

For Example:

If i found Door related services - Student Room in Report 1 then i should check in these mapping table and found corresponding Category and Sub Category and place it in that new appended table. 

Is there any option to try the above mentioned logic in PowerBI Report, please suggest any solution.

[Anonymous · Accepted solution]

Hi @Sabareeshwari 

You can refer to the code in power query.

Mapping Table

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jVDLCsIwEPyVoef6FXoRBaH1VnqIzaohNRs2qZK/N0Yp9CI9DMzuMg+266ods6ChUUXSaEmeZqCADdo4aXIRDfOjqiss0NddYefkCXzFPoSJ8rxVomECHEe8WKxxt+/WZytJmR95sFBO40ApzD4flCLl/Nfg19crI0t9yR45xNWJRXERtuRWa07xTjl4fkT/Bg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"CHC Service" = _t, Issues = _t, Dropdowns = _t, #"E-Facility Category" = _t, #"E-Facility Sub Category" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"CHC Service", type text}, {"Issues", type text}, {"Dropdowns", type text}, {"E-Facility Category", type text}, {"E-Facility Sub Category", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type"," ",null,Replacer.ReplaceValue,{"CHC Service"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value"," ",null,Replacer.ReplaceValue,{"Issues"}),
    #"Filled Down" = Table.FillDown(#"Replaced Value1",{"CHC Service", "Issues"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Issues] = "Type of Issue"))
in
    #"Filtered Rows"

Report 1

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("tZCxCoNAEER/ZdlaPyKYMiAk5WFxeEs89G5gd5P8vsHCJo0WqWfeDDMh8BVQUlmiSyITfedRjFp6+CtJdboDhRvuoiZaYM5DE7hDKah0UYk78zW1m3gwsfdJ1P4Rd2ZSNqpw+kDnXJ+n8f2Rg9Bmu2Gcf4qHFQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Category = _t, #"Sub Category" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Category", type text}, {"Sub Category", type text}}),
    #"Merged Queries" = Table.FuzzyNestedJoin(#"Changed Type", {"Category", "Sub Category"}, #"Mapping Table", {"CHC Service", "Dropdowns"}, "Mapping Table", JoinKind.LeftOuter, [IgnoreCase=true, IgnoreSpace=true]),
    #"Expanded Mapping Table" = Table.ExpandTableColumn(#"Merged Queries", "Mapping Table", {"E-Facility Category", "E-Facility Sub Category"}, {"E-Facility Category", "E-Facility Sub Category"})
in
    #"Expanded Mapping Table"

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.