The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hello,
Help needed... I have a quite messy complex CSV file that I'm trying to import to Power BI. I guess some pivoting/unpivoting and fill commands are needed, but I don't know how to get it done.
This is what the source CSV file looks like:
Category | Account | Text | Code | Amount | Unit | Price per unit | Total |
Financial account | 47787 | Org B | 4107871 | ||||
Main activity | Head org | AC.77 | 1 | pcs | 2750033 | ||
Resource | Res 12 | 4 | pcs | 50 | 200 | ||
Sub activity | Annual costs | 1 | year | 5794152 | 2749833 | ||
Sub activity:2 | Cat ABC | 2 | pcs | 433095 | 792075 | ||
Resource | Res 1 | 5 | pcs | 141415 | 707075 | ||
Resource | Res 44 | 1 | pcs | 85000 | 85000 | ||
Sub activity:2 | Cat DEF | 2 | pcs | 482091 | 498474 | ||
Resource | Res 45 | 45 | kg | 6890 | 310050 | ||
Resource | Res 48 | 1 | pcs | 188424 | 188424 | ||
Sub activity:2 | Cat EFG | 5 | pcs | 293423,4 | 82551 | ||
Resource | Res AB | 18 | m | 368 | 6624 | ||
Resource | Res R12 | 1 | pcs | 1452 | 1452 | ||
Resource | Res R11 | 3 | pcs | 22500 | 67500 | ||
Resource | Res R03 | 155 | m | 45 | 6975 | ||
Sub activity:2 | Tesla | 1 | pcs | 454920 | 454920 | ||
Resource | Res R99 | 1 | pcs | 454920 | 454920 | ||
Sub activity:2 | Chevy | 5 | pcs | 241108 | 523758 | ||
Resource | Res 3 | 2 | pcs | 261579 | 523158 | ||
Resource | Res 21 | 40 | m | 15 | 600 | ||
Sub activity:2 | Car 2 | 2 | pcs | 199027,5 | 398055 | ||
Resource | Res 98 | 1 | pcs | 170595 | 170595 | ||
Resource | Res T4 | 1 | pcs | 170595 | 170595 | ||
Resource | Res EE | 5 | pcs | 11373 | 56865 | ||
Main activity | Main act 3 | SS.50 | 1 | pcs | 1357838 | ||
Sub activity | Subact RR2 | 3 | pcs | 113730 | 342040 | ||
Resource | Res 5 | 2 | pcs | 170595 | 341190 | ||
Resource | Res GG | 1 | pcs | 850 | 850 | ||
Sub activity:2 | Annual cat B | 1 | year | 96960 | 96960 | ||
Resource | Res ert | 30 | weeks | 3232 | 96960 | ||
Sub activity | Res cat G | 1 | year | 1015798 | 1015798 | ||
Sub activity:2 | Res cat H | 1 | year | 529114 | 1015798 | ||
Sub activity:3 | Other res | 6 | pcs | 169300 | 1015798 | ||
Resource | Res JK | 2 | pcs | 33000 | 66000 | ||
Resource | Res RR | 22 | pcs | 25000 | 550000 | ||
Resource | Res RW | 14 | pcs | 28557 | 399798 | ||
Financial account | 11474 | Org A | 405910 | ||||
Main activity | Organization A | CB.33 | 1 | pcs | 405910 | ||
Sub activity | Act OOK | 1 | year | 405910 | 405910 | ||
Sub activity:2 | Subact AA | 2 | pcs | 201982 | 403964 | ||
Resource | Resource 22 | 1 | pcs | 348116 | 348116 | ||
Resource | Resource 23 | 1 | pcs | 55848 | 55848 | ||
Sub activity:2 | Act EETRE | 2 | pcs | 973 | 1946 | ||
Resource | Unit 556 | 3 | pcs | 554 | 1662 | ||
Resource | Unit 1323 | 2 | pcs | 142 | 284 |
And this is the goal after import:
Financial account | Code | Main activity | Sub activity | Sub activity:2 | Sub activity:3 | Resource | Amount | Unit | Price per unit |
47787 | AC.77 | Head org | Res 12 | 4 | pcs | 50 | |||
47787 | AC.77 | Head org | Annual costs | Cat ABC | Res 1 | 5 | pcs | 141415 | |
47787 | AC.77 | Head org | Annual costs | Cat ABC | Res 44 | 1 | pcs | 85000 | |
47787 | AC.77 | Head org | Annual costs | Cat DEF | Res 45 | 45 | kg | 6890 | |
47787 | AC.77 | Head org | Annual costs | Cat DEF | Res 48 | 1 | pcs | 188424 | |
47787 | AC.77 | Head org | Annual costs | Cat EFG | Res AB | 18 | m | 368 | |
47787 | AC.77 | Head org | Annual costs | Cat EFG | Res R12 | 1 | pcs | 1452 | |
47787 | AC.77 | Head org | Annual costs | Cat EFG | Res R11 | 3 | pcs | 22500 | |
47787 | AC.77 | Head org | Annual costs | Cat EFG | Res R03 | 155 | m | 45 | |
47787 | AC.77 | Head org | Annual costs | Tesla | Res R99 | 1 | pcs | 454920 | |
47787 | AC.77 | Head org | Annual costs | Chevy | Res 3 | 2 | pcs | 261579 | |
47787 | AC.77 | Head org | Annual costs | Chevy | Res 21 | 40 | m | 15 | |
47787 | AC.77 | Head org | Annual costs | Car 2 | Res 98 | 1 | pcs | 170595 | |
47787 | AC.77 | Head org | Annual costs | Car 2 | Res T4 | 1 | pcs | 170595 | |
47787 | AC.77 | Head org | Annual costs | Car 2 | Res EE | 5 | pcs | 11373 | |
47787 | AC.77 | Main act 3 | Subact RR2 | Car 2 | Res 5 | 2 | pcs | 170595 | |
47787 | AC.77 | Main act 3 | Subact RR2 | Car 2 | Res GG | 1 | pcs | 850 | |
47787 | AC.77 | Main act 3 | Subact RR2 | Annual cat B | Res ert | 30 | weeks | 3232 | |
47787 | AC.77 | Main act 3 | Res cat G | Res cat H | Other res | Res JK | 2 | pcs | 33000 |
47787 | AC.77 | Main act 3 | Res cat G | Res cat H | Other res | Res RR | 22 | pcs | 25000 |
47787 | AC.77 | Main act 3 | Res cat G | Res cat H | Other res | Res RW | 14 | pcs | 28557 |
11474 | CB.33 | Organization A | Act OOK | Subact AA | Resource 22 | 1 | pcs | 348116 | |
11474 | CB.33 | Organization A | Act OOK | Subact AA | Resource 23 | 1 | pcs | 55848 | |
11474 | CB.33 | Organization A | Act OOK | Act EETRE | Unit 556 | 3 | pcs | 554 | |
11474 | CB.33 | Organization A | Act OOK | Act EETRE | Unit 1323 | 2 | pcs | 142 |
Described in words: I wan't a table of all Resources with corresponding attributes which are fetched from lines above in the CSV.
Excel file with explainations, wanted result, hierarchy explanation
Solved! Go to Solution.
Hi @Daniel_L, different approach here.
In your expected result - there is a mistake in my opinion. This one should be correct:
Result
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jVZbb5swFP4rKM9R5dvxZW8kI6k2TZHSTnuo+sAy1KJ2ZCKkU/frd+zYQGuMqgh8IHznO3f77m6xqZuyOdTlc1YeDsdz0y2WC6GUVrju2odshWv27hKU4Ad0cb+8W3wr6wahXf1Sd6/+/+uq/JUd2wcU8/WVsqooXn8OJ7wzBYRw7j61CvbV6XhuD5XH4mNGWSDqUUAslBAHuTn/fE+ZN80ZfTgcT93Jv7KUr1XZWrQyggJz7MJoZH+v5lOgXJddlq/W/on1BgjOiQEUlGFEQdJ0L0MPpAJ/DkjUDFCIkdkXpMZAkX6dtfhzsYkt1owYqw09FkokiSEE2wpPNmlSG8vLKSFAkjgdGUy1FkwMwqzJxWYbxYoZLhhfWhWaAdAUdx6qklojfltbpZWkZEk/931VjQwWrijckoSFlPLBTAYuMdKWchJIeOAD8Fa6EEvjq2AyNLfV6bmMDBUgsOwGIcVpzIeg00l5rF5CO41SIiglNrbAuAKdYg6+DuXHJMW2uwBpGshCeAXxQXLNIucrvs1YREiNIUwtLZobTSDZamaichUB191eSCBv4yb9ILIo4sFAubJhA6klJGdpeOcifHNz5QbhiJ6D0vziUGI04iuL3+9DyPhbE1yrC0ZEsqzChGCx1xzLwySB29Dib4aavyfTG2Y5DonVCO9nuZFGkn5NEFdtF5y13/6tqidLzhlnb6AT4bJwS72NqSmxJa1HUtKHoOU61gLMUCrmlIRm2nWPVZu1VdjR5BB/abgbQWMdE3H48jVKHe5jl+Elyczw2gfcqKH9dgR2TSN/BI+HzZtpAOXa0gRbp44dGBVlQfbYkS8mjh1Yc5QkOwVhZVP/K7v62Dj8enXlzhmjWXjRMNMsOXbKbheiNspaDx1ZMZl532558GAUQEKNZk4FNzK1UbnHjMWbFReaUjkIs3AewQG027Iva7r50PaiuN0XkfnGTStqxCT196buMgAZzRgAV+u4NSdhlLN4/6DCndc0xun+Pw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Category = _t, Account = _t, Text = _t, Code = _t, Amount = _t, Unit = _t, #"Price per unit" = _t, Total = _t]),
ReplaceBlankToNull = Table.TransformColumns(Source, {}, each if _ is text and Text.Trim(_) = "" then null else _),
FilledDown = Table.FillDown(ReplaceBlankToNull,{"Account", "Code"}),
FilteredRows = Table.SelectRows(FilledDown, each not List.Contains({null, "Financial account"}, [Category])),
Categories = List.Buffer(List.Distinct(List.Select(FilteredRows[Category], each _ <> "Resource"))),
Ad_Columns = List.Accumulate(
Categories,
FilteredRows,
(s,c)=> Table.AddColumn(s, c, each if List.Contains({c, "Main activity"}, [Category]) then [Text] else null, type text) ),
FilledDown2 = Table.FillDown(Ad_Columns, Categories),
FilteredRows2 = Table.SelectRows(FilledDown2, each ([Category] = "Resource")),
ReplaceValues = Table.ReplaceValue(FilteredRows2,
each [Main activity],
each null,
(x,y,z)=> if x = y then z else x,
List.Skip(Categories) ),
RestoreTypes = Value.ReplaceType(ReplaceValues, Value.Type(FilteredRows2)),
RemovedColumns = Table.RemoveColumns(RestoreTypes,{"Category", "Total"}),
RenamedColumns = Table.RenameColumns(RemovedColumns,{{"Account", "Financial account"}, {"Text", "Resource"}}),
ReorderedColumnsDynamic = Table.ReorderColumns(RenamedColumns,{"Financial account", "Code"} & Categories & {"Resource", "Amount", "Unit", "Price per unit"}),
ChangedType = Table.TransformColumnTypes(ReorderedColumnsDynamic,{{"Financial account", Int64.Type}, {"Code", type text}, {"Resource", type text}, {"Amount", type number}, {"Unit", type text}, {"Price per unit", Currency.Type}})
in
ChangedType
Hi @Daniel_L, different approach here.
In your expected result - there is a mistake in my opinion. This one should be correct:
Result
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jVZbb5swFP4rKM9R5dvxZW8kI6k2TZHSTnuo+sAy1KJ2ZCKkU/frd+zYQGuMqgh8IHznO3f77m6xqZuyOdTlc1YeDsdz0y2WC6GUVrju2odshWv27hKU4Ad0cb+8W3wr6wahXf1Sd6/+/+uq/JUd2wcU8/WVsqooXn8OJ7wzBYRw7j61CvbV6XhuD5XH4mNGWSDqUUAslBAHuTn/fE+ZN80ZfTgcT93Jv7KUr1XZWrQyggJz7MJoZH+v5lOgXJddlq/W/on1BgjOiQEUlGFEQdJ0L0MPpAJ/DkjUDFCIkdkXpMZAkX6dtfhzsYkt1owYqw09FkokiSEE2wpPNmlSG8vLKSFAkjgdGUy1FkwMwqzJxWYbxYoZLhhfWhWaAdAUdx6qklojfltbpZWkZEk/931VjQwWrijckoSFlPLBTAYuMdKWchJIeOAD8Fa6EEvjq2AyNLfV6bmMDBUgsOwGIcVpzIeg00l5rF5CO41SIiglNrbAuAKdYg6+DuXHJMW2uwBpGshCeAXxQXLNIucrvs1YREiNIUwtLZobTSDZamaichUB191eSCBv4yb9ILIo4sFAubJhA6klJGdpeOcifHNz5QbhiJ6D0vziUGI04iuL3+9DyPhbE1yrC0ZEsqzChGCx1xzLwySB29Dib4aavyfTG2Y5DonVCO9nuZFGkn5NEFdtF5y13/6tqidLzhlnb6AT4bJwS72NqSmxJa1HUtKHoOU61gLMUCrmlIRm2nWPVZu1VdjR5BB/abgbQWMdE3H48jVKHe5jl+Elyczw2gfcqKH9dgR2TSN/BI+HzZtpAOXa0gRbp44dGBVlQfbYkS8mjh1Yc5QkOwVhZVP/K7v62Dj8enXlzhmjWXjRMNMsOXbKbheiNspaDx1ZMZl532558GAUQEKNZk4FNzK1UbnHjMWbFReaUjkIs3AewQG027Iva7r50PaiuN0XkfnGTStqxCT196buMgAZzRgAV+u4NSdhlLN4/6DCndc0xun+Pw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Category = _t, Account = _t, Text = _t, Code = _t, Amount = _t, Unit = _t, #"Price per unit" = _t, Total = _t]),
ReplaceBlankToNull = Table.TransformColumns(Source, {}, each if _ is text and Text.Trim(_) = "" then null else _),
FilledDown = Table.FillDown(ReplaceBlankToNull,{"Account", "Code"}),
FilteredRows = Table.SelectRows(FilledDown, each not List.Contains({null, "Financial account"}, [Category])),
Categories = List.Buffer(List.Distinct(List.Select(FilteredRows[Category], each _ <> "Resource"))),
Ad_Columns = List.Accumulate(
Categories,
FilteredRows,
(s,c)=> Table.AddColumn(s, c, each if List.Contains({c, "Main activity"}, [Category]) then [Text] else null, type text) ),
FilledDown2 = Table.FillDown(Ad_Columns, Categories),
FilteredRows2 = Table.SelectRows(FilledDown2, each ([Category] = "Resource")),
ReplaceValues = Table.ReplaceValue(FilteredRows2,
each [Main activity],
each null,
(x,y,z)=> if x = y then z else x,
List.Skip(Categories) ),
RestoreTypes = Value.ReplaceType(ReplaceValues, Value.Type(FilteredRows2)),
RemovedColumns = Table.RemoveColumns(RestoreTypes,{"Category", "Total"}),
RenamedColumns = Table.RenameColumns(RemovedColumns,{{"Account", "Financial account"}, {"Text", "Resource"}}),
ReorderedColumnsDynamic = Table.ReorderColumns(RenamedColumns,{"Financial account", "Code"} & Categories & {"Resource", "Amount", "Unit", "Price per unit"}),
ChangedType = Table.TransformColumnTypes(ReorderedColumnsDynamic,{{"Financial account", Int64.Type}, {"Code", type text}, {"Resource", type text}, {"Amount", type number}, {"Unit", type text}, {"Price per unit", Currency.Type}})
in
ChangedType
After viewing the provided solution a bit more I also see that SubActivity:2 is missing SubActivity. I.e. "Cat DEF" should have SubActivity = "Annual costs".
I have changed the Code, please check it again.
Wow, thank you very much for providing this solution! Just one minor detail missing, the column for the resource name. I guess I'll figure that out.
let
Source = Csv.Document(File.Contents("Your_Csv_File_Address"),[Delimiter=";", Columns=10,QuoteStyle=QuoteStyle.None]),
Custom1 = let
t=Table.PromoteHeaders(Source)
in
Table.FromPartitions(
"Financial Account",
Table.ToRows(
Table.Group(
t,
"Account",
{
"c1",
each Table.Combine(
Table.Group(
Table.Skip(_),
"Category",
{
"c2",
each let
a=Table.Group(
Table.Skip(_),
"Category",
{"c3",each _},
0,
(x,y)=>Byte.From((Text.StartsWith(x,"Sub activity") and Text.StartsWith(y,"Resource")) or (Text.StartsWith(y,"Sub activity") and Text.StartsWith(x,"Resource")))
),
b=[Main activity=[Text]{0},Code=[Code]{0}]
in
List.Accumulate(
Table.ToRows(a),
{#table(0,{}),b},
(x,y)=>if Text.StartsWith(y{0},"Resource")
then {x{0}&Table.FromRecords(Table.TransformRows(y{1},each x{1}&[Resource=[Text]]&[[Amount],[Unit],[Price per unit],[Total]])),x{1}}
else {x{0},x{1}&Record.FromTable(Table.FromColumns({y{1}[Category],y{1}[Text]},{"Name","Value"}))}
){0}
},
0,
(x,y)=>Byte.From(Text.StartsWith(y,"Main activity"))
)[c2]
)
},
0,
(x,y)=>Byte.From(y<>"" and y<>null)
)
)
)
in
Custom1
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
36 | |
23 | |
23 | |
18 | |
16 |