Forum Discussion
New rows with inhereted values
Hello,
got table
| Period | Entity | Value |
| NOV 2020 | Entity 1 | 20 |
| DEC 2020 | Entity 1 | 30 |
| JAN 2021 | Entity 1 | 25 |
Now I want to insert row for every period with same value but different entity, so my table looks like this:
| Period | Entity | Value |
| NOV 2020 | Entity 1 | 20 |
| NOV 2020 | Entity 2 | 20 |
| DEC 2020 | Entity 1 | 30 |
| DEC 2020 | Entity 2 | 30 |
| JAN 2021 | Entity 1 | 25 |
| JAN 2021 | Entity 2 | 25 |
Is it something I have to do in query editor or just through new measure after data load?
Basically what I really need is to present in visual "Entity 2" with same values as "Entity 1" for multiple preiods.
Any ideas?
Thank you
If you just want the data table twice as large kyon84 with Entity 1 being replaced by Entity 2 in the rest of the data, see this code:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8vMPUzAyMDJQ0lFyzSvJLKlUMAQygfxYnWglF1dnLJLGEEkvRz+QpCGSpAJIq6lSbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Period = _t, Entity = _t, Value = _t]), #"Replaced Value" = Table.ReplaceValue(Source,"Entity 1","Entity 2",Replacer.ReplaceText,{"Entity"}), #"Appended Query" = Table.Combine({Source, #"Replaced Value"}) in #"Appended Query"The source line is your original data.
Then I replace Entity 1 with Entity 2
Then I append Entity 1 (going back to the source line) with the Replaced Value step. It returns this:
How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.
2 Replies
If you just want the data table twice as large kyon84 with Entity 1 being replaced by Entity 2 in the rest of the data, see this code:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8vMPUzAyMDJQ0lFyzSvJLKlUMAQygfxYnWglF1dnLJLGEEkvRz+QpCGSpAJIq6lSbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Period = _t, Entity = _t, Value = _t]), #"Replaced Value" = Table.ReplaceValue(Source,"Entity 1","Entity 2",Replacer.ReplaceText,{"Entity"}), #"Appended Query" = Table.Combine({Source, #"Replaced Value"}) in #"Appended Query"The source line is your original data.
Then I replace Entity 1 with Entity 2
Then I append Entity 1 (going back to the source line) with the Replaced Value step. It returns this:
How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.only as exercise
let Origine = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8vMPUzAyMDJQ0lFyzSvJLKlUMAQygfxYnWglF1dnLJLGEEkvRz+QpCGaTlOl2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Period = _t, Entity = _t, Value = _t]), #"Raggruppate righe" = Table.Group(Origine, {"Period", "Value"}, {{"ent", each let last=Text.From(Number.From(Text.End(List.Last(_[Entity]),1))+1) in _[Entity]&{"Entity "&last}}}), #"Tabella ent espansa" = Table.ExpandListColumn(#"Raggruppate righe", "ent") in #"Tabella ent espansa"just as an exercise, in case the problem is slightly more general ...
