Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello,
I need your support for the next topic:
I have 2 initial tables:
Tab 1: "List of suppliers"
Tab 2: "List of audit performed" - Note: not all the suppliers from Tab 1 are included in Tab 2 - depends if the audit "Type X" was performed or not.
I want to find the best way to generate a third table based on the 2 previous one, which will represent, for each "Audit" type, the audit "Status" for each supplier "Name" in the Tab 1. If no "Status" found in Tab 2, write "Not done".
Here is a simplified representation:
Thanks and best regards.
Solved! Go to Solution.
@Anonymous , Create a new table
NewTable = crossjoin(suppliers, Distinct(Audit[Audit type]))
and add this new column
new column = coalesce(maxx(filter(Audit, Audit[suppliers] =NewTable[suppliers] && Audit[Audit type] =NewTable[Audit type]),[Audit Type]),"Not Done")
You can merge in power query too-https://radacad.com/append-vs-merge-in-power-bi-and-power-query
@Anonymous
Duplicate the Supplier Table and do the transformation:
You can download the file:HERE
________________________
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Hi @Anonymous
you can do it in Power Query like this:
// Audit list
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUQqpLEhVMAQy/AtS85RidaKVnJBFnXPyi1NTwOLOWFXDzTDCaoYRDjNQVLvARI2xiprARWMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Audit = _t, Status = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Audit", type text}, {"Status", type text}}),
#"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"Audit"}),
#"Removed Duplicates" = Table.Distinct(#"Removed Other Columns"),
#"Added Custom" = Table.AddColumn(#"Removed Duplicates", "Custom", each #"Name list"),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Name"}, {"Name"}),
#"Reordered Columns" = Table.ReorderColumns(#"Expanded Custom",{"Name", "Audit"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Reordered Columns",{{"Name", type text}}),
#"Merged Queries" = Table.NestedJoin(#"Changed Type1", {"Name", "Audit"}, #"List of audit performed", {"Name", "Audit"}, "List of audit performed", JoinKind.LeftOuter),
#"Expanded List of audit performed" = Table.ExpandTableColumn(#"Merged Queries", "List of audit performed", {"Status"}, {"Status"}),
#"Replaced Value" = Table.ReplaceValue(#"Expanded List of audit performed",null,"Not done",Replacer.ReplaceValue,{"Status"})
in
#"Replaced Value"
// Name list
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUVKK1YlWcoIxnGEMFxjDFcZwAzNiAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Region = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Region", type text}}),
#"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"Name"}),
#"Removed Duplicates" = Table.Distinct(#"Removed Other Columns")
in
#"Removed Duplicates"
// List of audit performed
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUQqpLEhVMAQy/AtS85RidaKVnJBFnXPyi1NTwOLOWFXDzTDCaoYRDjNQVLvARI2xiprARWMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Audit = _t, Status = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Audit", type text}, {"Status", type text}})
in
#"Changed Type"
// List of suppliers
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUVKK1YlWcoIxnGEMFxjDFcZwAzNiAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Region = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Region", type text}})
in
#"Changed Type"
With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)
@Anonymous , Create a new table
NewTable = crossjoin(suppliers, Distinct(Audit[Audit type]))
and add this new column
new column = coalesce(maxx(filter(Audit, Audit[suppliers] =NewTable[suppliers] && Audit[Audit type] =NewTable[Audit type]),[Audit Type]),"Not Done")
You can merge in power query too-https://radacad.com/append-vs-merge-in-power-bi-and-power-query
@Anonymous Seems like you should use a Merge query in Power Query Editor
Hello Greg,
I'm not sure that i know how to do it.. Can you help with details please ?
Thanks.
User | Count |
---|---|
128 | |
108 | |
100 | |
64 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |