TRUE/FALSE into values

[Anonymous]

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 

[Anonymous]

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"

[Nathaniel_C]

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.
Nathaniel

[Anonymous · Accepted solution]

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