Forum Discussion
Helper IConundrum Power Query
Hi everyone,
I am a bit new to Power Query and have a requirement which i am unable to figure out with my limited knowledge. Got a simple table with multiple records which I require to "explode" into a new table based on some text based conditions.
The source data (pretty simple);
| No | Totalling |
| 10001 | |
| 10002 | 10001|10004..10005 |
| 10003 | 10001|10002 |
| 10004 | 10002..10003 |
The aim is to create a sub table in support of generating DAX measures totaling related entries within another related table. As DAX has limited functonality in relation to creating tables and rows I am investigating generating a new table within M.
The aim would be creating a second table within M. This second table will then we be referenced to the first table.
| No | TotalFrom | TotalTo |
| 10002 | 10001 | 10001 |
| 10002 | 10004 | 10005 |
| 10003 | 10001 | 10001 |
| 10003 | 10002 | 10002 |
| 10004 | 10002 | 10003 |
The key is here the use of the "|" in combination with ".." characters use. The challenging bit for me how i am able to drive the Table.InsertRows following such condition(s). The "|" means a new row whilst the use of ".." would need to drive the value within the TotalTo column.
From reading the interwebs, forums and available documentation it should be possible to loop through the source table using a list. This loop then could drive the required conditions. Is this something that should be possible? Maybe someone has solved this requirement already? No iam not asking for a final solution, would like to enage with you guys to get some idea how to solve this.
Thanks..
Hi Mark1982 ,
Use this mcode:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwMDBU0lFSUIrVgfCMgDywaA2INNHTA1GmcGljFGkjuLgJVNwIosFYKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [No = _t, Totalling = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"No", Int64.Type}, {"Totalling", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.Split([Totalling], "|")), #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"), #"Split Column by Delimiter" = Table.SplitColumn(#"Expanded Custom", "Custom", Splitter.SplitTextByDelimiter("..", QuoteStyle.Csv), {"Custom.1", "Custom.2"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Custom.1", Int64.Type}, {"Custom.2", type text}}), #"Added Custom1" = Table.AddColumn(#"Changed Type1", "Custom", each if [Custom.2] = null then [Custom.1] else [Custom.2]), #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Custom.2"}), #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([Totalling] <> " ")), #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows",{"Totalling"}), #"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",{{"Custom.1", "TotalFrom"}, {"Custom", "TotalTo"}}), #"Changed Type2" = Table.TransformColumnTypes(#"Renamed Columns",{{"TotalTo", Int64.Type}}) in #"Changed Type2"
3 Replies
camargos88Community Champion
Hi Mark1982 ,
Use this mcode:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwMDBU0lFSUIrVgfCMgDywaA2INNHTA1GmcGljFGkjuLgJVNwIosFYKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [No = _t, Totalling = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"No", Int64.Type}, {"Totalling", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.Split([Totalling], "|")), #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"), #"Split Column by Delimiter" = Table.SplitColumn(#"Expanded Custom", "Custom", Splitter.SplitTextByDelimiter("..", QuoteStyle.Csv), {"Custom.1", "Custom.2"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Custom.1", Int64.Type}, {"Custom.2", type text}}), #"Added Custom1" = Table.AddColumn(#"Changed Type1", "Custom", each if [Custom.2] = null then [Custom.1] else [Custom.2]), #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Custom.2"}), #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([Totalling] <> " ")), #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows",{"Totalling"}), #"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",{{"Custom.1", "TotalFrom"}, {"Custom", "TotalTo"}}), #"Changed Type2" = Table.TransformColumnTypes(#"Renamed Columns",{{"TotalTo", Int64.Type}}) in #"Changed Type2"
Mark1982I've spend some time disecting your proposed solution. The = Table.AddColumn(#"Changed Type", "Custom", each Text.Split([Totaling], "|")) and = Table.SplitColumn(#"Expanded Custom", "Custom", Splitter.SplitTextByDelimiter("..", QuoteStyle.Csv), {"Custom.1", "Custom.2"}) are the holy grail.
Thank you very much.
I tried to guess what the input table looks like.
You try this:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwMDBU0lFSUIrVgfCMgDywaA2INNHTA1GmcGljFGkjuLgJVNwIosFYKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [No = _t, Totalling = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"No", Int64.Type}, {"Totalling", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "tot fromto", each Text.Split([Totalling],"|")), #"Expanded tot fromto" = Table.ExpandListColumn(#"Added Custom", "tot fromto"), #"Split Column by Delimiter" = Table.SplitColumn(#"Expanded tot fromto", "tot fromto", Splitter.SplitTextByDelimiter("..", QuoteStyle.Csv), {"tot fromto.1", "tot fromto.2"}), #"Changed Type1" = Table.FromRecords(Table.TransformRows(#"Split Column by Delimiter", each _& [#"tot fromto.2"=_[tot fromto.2]? ??[tot fromto.1]] )) in #"Changed Type1"
