Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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 ...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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"