Forum Discussion
TRUE/FALSE into values
Hi guys
I hope someone will be able to help me or tell me its not possible.
I have data like this. Where the hierarchy is MotherID->Sold To-> Shipto, where one Mother ID can have multiple Sold Tos and Shiptos. SoldTo can have multiple Shiptos but only One Mother ID and one Shipto can belong to only one Sold to and only one Mother ID.
| Acct ID | MotherID | Sold To | Shipto |
| A | X | X | X |
| B | X | X | |
| C | X | ||
| D | X | ||
| E | X | X | X |
| F | X | ||
| G | X | X | X |
| H | X | X | X |
| I | X | X | |
| J | X | ||
| K | X | ||
| L | X |
I would like to transfer the data into something like this. Where if the Mother ID has multiple SoldTos or Shiptos it will be repeated, if Solds to Has multiple shiptos it will be repeated.
| MotherID | Sold To | Shipto |
| A | A | A |
| A | B | B |
| A | B | C |
| A | B | D |
| E | E | E |
| E | E | F |
| G | G | G |
| H | H | H |
| H | I | I |
| H | I | J |
| H | I | K |
| H | I | L |
Hope this makes sence.
I am happy for any opinion.
Thank you
Hi Nathaniel,
I looked at your solution and found it thoughtful. However, you may want to reconsider the use of the tranpose/remove column/transopose construct as it appears to be incorrect. I think the transform is to replace the X with the ACCTID and then fill down. So if the X's are moved around, then the remove columns step between the tranposes will fail. Below is a modification of your Origin (2) code that uses SelectRows. Orgin (3) and Origin(4) would need to be rewritten similarly
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUYqA41idaCUnIEsBRcQZKqIAF3HBEHHFMMcNQ407hhoPDBFPDNu9MMzxxhDxQRWJBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Acct ID" = _t, MotherID = _t, #"Sold To" = _t, Shipto = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Acct ID", type text}, {"MotherID", type text}, {"Sold To", type text}, {"Shipto", type text}}), #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Sold To", "Shipto"}), #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([MotherID] = "X")), #"Removed Columns2" = Table.RemoveColumns(#"Filtered Rows",{"MotherID"}), #"Renamed Columns" = Table.RenameColumns(#"Removed Columns2",{{"Acct ID", "Mother"}}), #"Merged Queries" = Table.NestedJoin(#"Renamed Columns", {"Mother"}, #"ORIGIN (3)", {"Sold to"}, "ORIGIN (3)", JoinKind.RightOuter), #"Expanded ORIGIN (3)" = Table.ExpandTableColumn(#"Merged Queries", "ORIGIN (3)", {"Sold to"}, {"Sold to"}), #"Merged Queries1" = Table.NestedJoin(#"Expanded ORIGIN (3)", {"Sold to"}, #"ORIGIN (4)", {"Ship To"}, "ORIGIN (4)", JoinKind.RightOuter), #"Expanded ORIGIN (4)" = Table.ExpandTableColumn(#"Merged Queries1", "ORIGIN (4)", {"Ship To"}, {"Ship To"}), #"Sorted Rows" = Table.Sort(#"Expanded ORIGIN (4)",{{"Ship To", Order.Ascending}}), #"Filled Down" = Table.FillDown(#"Sorted Rows",{"Sold to", "Mother"}) in #"Filled Down"Hope you do not mind my feedback,
Mike
3 Replies
Hi Anonymous,
I believe the picture is what you are looking to see. Mother is my pbix
If you go to Power Query, you will see my steps.
Created 3 tables, and transposed them, any columns that did not have X, I deleted, then transposed back. Then merged 2 and 3, and then merged that with 4. The table 2 is the full table. When joined, I use the Fill button to Fill Down. Learned a lot of this from M is for (Data) Monkey by KenPuls . Kind of fun.
Let me know if you have any questions.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
NathanielHi Nathaniel,
I looked at your solution and found it thoughtful. However, you may want to reconsider the use of the tranpose/remove column/transopose construct as it appears to be incorrect. I think the transform is to replace the X with the ACCTID and then fill down. So if the X's are moved around, then the remove columns step between the tranposes will fail. Below is a modification of your Origin (2) code that uses SelectRows. Orgin (3) and Origin(4) would need to be rewritten similarly
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUYqA41idaCUnIEsBRcQZKqIAF3HBEHHFMMcNQ407hhoPDBFPDNu9MMzxxhDxQRWJBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Acct ID" = _t, MotherID = _t, #"Sold To" = _t, Shipto = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Acct ID", type text}, {"MotherID", type text}, {"Sold To", type text}, {"Shipto", type text}}), #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Sold To", "Shipto"}), #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([MotherID] = "X")), #"Removed Columns2" = Table.RemoveColumns(#"Filtered Rows",{"MotherID"}), #"Renamed Columns" = Table.RenameColumns(#"Removed Columns2",{{"Acct ID", "Mother"}}), #"Merged Queries" = Table.NestedJoin(#"Renamed Columns", {"Mother"}, #"ORIGIN (3)", {"Sold to"}, "ORIGIN (3)", JoinKind.RightOuter), #"Expanded ORIGIN (3)" = Table.ExpandTableColumn(#"Merged Queries", "ORIGIN (3)", {"Sold to"}, {"Sold to"}), #"Merged Queries1" = Table.NestedJoin(#"Expanded ORIGIN (3)", {"Sold to"}, #"ORIGIN (4)", {"Ship To"}, "ORIGIN (4)", JoinKind.RightOuter), #"Expanded ORIGIN (4)" = Table.ExpandTableColumn(#"Merged Queries1", "ORIGIN (4)", {"Ship To"}, {"Ship To"}), #"Sorted Rows" = Table.Sort(#"Expanded ORIGIN (4)",{{"Ship To", Order.Ascending}}), #"Filled Down" = Table.FillDown(#"Sorted Rows",{"Sold to", "Mother"}) in #"Filled Down"Hope you do not mind my feedback,
Mike
Here is another approach
let Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content], XFields = List.Buffer(List.Select(Table.ColumnNames(Source), each _ <> "Acct ID")), TFields = List.Buffer(List.Transform(XFields, each "t" & _)), AddCols = List.Accumulate(List.Positions(XFields), Source, (s,c) => Table.AddColumn(s, TFields{c}, each if Record.Field(_,XFields{c}) ="X" then [Acct ID] else null )), #"Removed Columns" = Table.SelectColumns(AddCols,TFields), FillDown = Table.FillDown(#"Removed Columns",TFields), #"Renamed Columns" = Table.RenameColumns(FillDown,List.Zip({TFields,XFields})) in #"Renamed Columns"
