Forum Discussion
henry24007
4 years agoHelper I
insert a blank row conditionally in PQ
| col1 | index | value |
| a | 1 | 9 |
| b | 2 | 8 |
| sub-total | 3 | 7 |
| c | 4 | 6 |
| sub-total | 5 | 5 |
| d | 6 | 4 |
| e | 7 | 3 |
| sub-total | 8 | 1 |
| total | 9 | 1 |
How do I insert a blank row when it meet every sub-total , or when it comes to index 3,5,7 it will create a blank row look like below:
| col1 | index | value |
| a | 1 | 9 |
| b | 2 | 8 |
| sub-total | 3 | 7 |
| c | 4 | 6 |
| sub-total | 5 | 5 |
| d | 6 | 4 |
| e | 7 | 3 |
| sub-total | 8 | 2 |
| total | 9 | 1 |
Here try this:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUTIEYkulWJ1opSQgywiILcC84tIk3ZL8ksQcoIgxEJuDRZOBLBMgNsNQYwrGINEUsDxIHYiXCtYLMgNdhwXYdpAoTMQSIhILAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [col1 = _t, index = _t, value = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"col1", type text}, {"index", Int64.Type}, {"value", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [col1] = "sub-total" then "1"&Text.Repeat(" @1",1) else [col1]), #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Added Custom", {{"index", type text}, {"value", type text}}, "en-US"),{"col1", "index", "value"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"Merged"), #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Merged Columns", {{"Custom", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Custom"), #"Added Custom1" = Table.AddColumn(#"Split Column by Delimiter", "Custom.1", each if[Custom] ="@1" then null else [Merged]), #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Merged", "Custom"}), #"Split Column by Delimiter1" = Table.SplitColumn(#"Removed Columns", "Custom.1", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Custom.1.1", "Custom.1.2", "Custom.1.3"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Custom.1.1", type text}, {"Custom.1.2", Int64.Type}, {"Custom.1.3", Int64.Type}}), #"Replaced Value" = Table.ReplaceValue(#"Changed Type1",null,"",Replacer.ReplaceValue,{"Custom.1.1", "Custom.1.2", "Custom.1.3"}), #"Renamed Columns" = Table.RenameColumns(#"Replaced Value",{{"Custom.1.1", "col1"}, {"Custom.1.2", "Index"}, {"Custom.1.3", "Value"}}) in #"Renamed Columns"Outcome like this:
2 Replies
- PC2790Community Champion
Here try this:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUTIEYkulWJ1opSQgywiILcC84tIk3ZL8ksQcoIgxEJuDRZOBLBMgNsNQYwrGINEUsDxIHYiXCtYLMgNdhwXYdpAoTMQSIhILAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [col1 = _t, index = _t, value = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"col1", type text}, {"index", Int64.Type}, {"value", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [col1] = "sub-total" then "1"&Text.Repeat(" @1",1) else [col1]), #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Added Custom", {{"index", type text}, {"value", type text}}, "en-US"),{"col1", "index", "value"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"Merged"), #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Merged Columns", {{"Custom", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Custom"), #"Added Custom1" = Table.AddColumn(#"Split Column by Delimiter", "Custom.1", each if[Custom] ="@1" then null else [Merged]), #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Merged", "Custom"}), #"Split Column by Delimiter1" = Table.SplitColumn(#"Removed Columns", "Custom.1", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Custom.1.1", "Custom.1.2", "Custom.1.3"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Custom.1.1", type text}, {"Custom.1.2", Int64.Type}, {"Custom.1.3", Int64.Type}}), #"Replaced Value" = Table.ReplaceValue(#"Changed Type1",null,"",Replacer.ReplaceValue,{"Custom.1.1", "Custom.1.2", "Custom.1.3"}), #"Renamed Columns" = Table.RenameColumns(#"Replaced Value",{{"Custom.1.1", "col1"}, {"Custom.1.2", "Index"}, {"Custom.1.3", "Value"}}) in #"Renamed Columns"Outcome like this:
- AnonymousNot applicable
Hi henry24007,
Did the above suggestion help with your scenario? if that is the case, you can consider Kudo or accept it to help others who faced similar requirements.
If these also don't help, please share more detailed information about your requirements to help us clarify your scenario and test.
How to Get Your Question Answered Quickly
Regards,
Xiaoxin Sheng